﻿using Coldairarrow.Entity;
using Coldairarrow.Entity.Base;
using Coldairarrow.Entity.Base_Manage;
using Coldairarrow.Entity.DTO;
using Coldairarrow.Entity.IT;
using Coldairarrow.Entity.PB;
using Coldairarrow.Entity.Print;
using Coldairarrow.Entity.TD;
using Coldairarrow.IBusiness;
using Coldairarrow.IBusiness.DTO;
using Coldairarrow.Util;
using Coldairarrow.Util.ClassLibrary;
using Coldairarrow.Util.Helper;
using EFCore.Sharding;
using LinqKit;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace Coldairarrow.Business.PB
{
    public partial class PB_DispatchBusiness : BaseBusiness<PB_Dispatch>, IPB_DispatchBusiness, ITransientDependency

    {
        readonly IOperator _operator;
        readonly ICheckOperation _check;
        readonly IOperatorDept _opdept;
        
        public PB_DispatchBusiness(IDbAccessor db, IOperator @operator, ICheckOperation @check, IOperatorDept @opdept)
            : base(db)
        {
            _check = @check;
            _operator = @operator;
            _opdept = @opdept;
        }

        #region 外部接口

        public async Task<PageResult<PB_Dispatch>> GetDataListAsync(PageInput<ConditionDTO> input)
        {
            var q = GetIQueryable();
            var where = LinqHelper.True<PB_Dispatch>();
            var search = input.Search;

            //筛选
            if (!search.Condition.IsNullOrEmpty() && !search.Keyword.IsNullOrEmpty())
            {
                var newWhere = DynamicExpressionParser.ParseLambda<PB_Dispatch, bool>(
                    ParsingConfig.Default, false, $@"{search.Condition}.Contains(@0)", search.Keyword);
                where = where.And(newWhere);
            }

            q = q.Where(x => _operator.Property.DeptIds.Contains(x.DeptId));
            return await q.Where(where).GetPageResultAsync(input);
        }

        public async Task<PageResult<PB_DispatchDTO>> GetDispatchDataListAsync(PageInput<PB_DispatchConditionDTO> input)
        {
            var search = input.Search;
            Expression<Func<PB_Dispatch, Base_Department, PB_ShiftType, Base_User,PB_DispatchDTO>> select = (a, b, c,d) => new PB_DispatchDTO
            {
                DepartmentName = b.Name,
                TeamTypeName = c.Name,
                UserCode=d.UserCode,
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in GetIQueryable().AsExpandable()
                    join b in Db.GetIQueryable<Base_Department>() on a.DeptId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<PB_ShiftType>() on a.TeamType equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<Base_User>() on a.StaffId equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    select @select.Invoke(a, b, c,d);
            if (!search.Keyword.IsNullOrEmpty())
            {
                var keyword = $"%{search.Keyword}%";
                q = q.Where(x =>
                      EF.Functions.Like(x.StaffName, keyword)
                    || EF.Functions.Like(x.UserCode, keyword));
            }
            if (!search.StartDate.IsNullOrEmpty() && !search.EndDate.IsNullOrEmpty())
            {
                q = q.Where(x => (search.StartDate <= x.DispatchDate) && (x.DispatchDate <= search.EndDate));
            }
            if (!search.DepartmentName.IsNullOrEmpty()) {
                q = q.Where(x=>x.DepartmentName==search.DepartmentName);
            }
            q = q.Where(x => _operator.Property.DeptIds.Contains(x.DeptId));
           
             await q.GetPageResultAsync(input);
            var AA =await q.GetPageResultAsync(input);
            foreach (var item in AA.Data) {     
                string sql = $"SELECT b.`Name` ProcecureSubName ,IFNULL(SUM(a.Nums),0)Nums FROM  PB_DispatchDetail as a  LEFT JOIN PB_ProcecureSub AS b on a.SubId = b.Id WHERE a.ParentId = '{item.Id}' GROUP BY a.SubId";
                item.DispatchDetailsub = await Db.GetListBySqlAsync<PB_DispatchDetailsub>(sql);
            }
            return AA;

        }

        public async Task<PB_Dispatch> GetTheDataAsync(string id)
        {
            return await GetEntityAsync(id);
        }

        public async Task AddDataAsync(PB_Dispatch data)

        {
            data.QrCode = h_DispatchDetail + "," +data.Id;
            await InsertAsync(data);
        }

        public async Task UpdateDataAsync(PB_Dispatch data)
        {
            await UpdateAsync(data);
        }

        /// <summary>
        /// 删除派工信息
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public async Task DeleteDataAsync(List<string> ids)
        {
            string detailIds = string.Join("','", ids.ToArray());
            string sqlMain = @"select * from PB_CirculationBill where DispatchId in ('" + detailIds + "')";
            List<PB_CirculationBill> listdata = await Db.GetListBySqlAsync<PB_CirculationBill>(sqlMain);
            if (listdata.Count > 0)
            {
                throw new BusException("有派工单已有报工,不能删除！");
            }
            sqlMain = @" delete from PB_DispatchDetail where ParentId in ('" + detailIds + "') ";
            await ExecuteSqlAsync(sqlMain);
            await DeleteAsync(ids);
        }

        /// <summary>
        /// 派工选择人员信息
        /// </summary>
        /// <param name="dispatchDate">派工日期</param>
        /// <returns></returns>
        public async Task<List<ScheduleDetailDTO>> GetScheduleDetailUserListAsync(string dispatchDate)
        {
          
            string WorkShopId = _operator.Property?.DepartmentId;
            string sqlMain = @"select a.Id,a.UserId,d.RealName,a.Type,b.Name as TypeName,b.WorkShopID,e.`Name` as DepartmentName,a.WorkDate,role.RoleId roleId  from PB_ScheduleDetail as a left join PB_ShiftType as b on a.Type=b.Id left join PB_Schedule as c on a.ParentId=c.Id left join Base_User as d on a.UserId=d.Id LEFT join Base_Department as e on b.WorkShopID=e.Id left JOIN Base_UserRole AS role on d.ID= role.UserId where c.Toexamine=2 ";
           
            if (!dispatchDate.IsNullOrEmpty())
            {
                sqlMain += @"and a.WorkDate='" + dispatchDate + "' ";
            }
            //if (!search.TypeId.IsNullOrEmpty())
            //{
            //    sqlMain += @"and a.Type='" + search.TypeId + "' ";
            //}
            //if (!search.Keyword.IsNullOrEmpty())
            //{
            //    sqlMain += @"and d.RealName like '%" + search.Keyword + "%' ";
            //}

            //if (!search.Roid.IsNullOrEmpty())
            //{
            //    if (search.Roid.Count > 0)
            //    {
            //        string roid = "";
            //        foreach (var item in search.Roid)
            //        {
            //            roid += item + "'" + "," + "'";

            //        }
            //        roid = roid.Substring(0, roid.Length - 3);
            //        sqlMain += @" and (role.RoleId in('" + roid + "'))";
            //    }
            //}
            List<ScheduleDetailDTO> listdata = await Db.GetListBySqlAsync<ScheduleDetailDTO>(sqlMain);
            int num = listdata.Count;


            return listdata;
        }

        public async Task<Object> GetUserWhetherDispatchAsync(string dispatchDate, string userid)
        {
            var q = await Db.GetIQueryable<PB_Dispatch>().Where(x => Convert.ToDateTime(dispatchDate) <= x.DispatchDate && x.DispatchDate <= Convert.ToDateTime(dispatchDate).AddDays(1).AddSeconds(-1) && x.StaffId == userid).ToListAsync();
            if (q.Count() > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 派工获取工序信息
        /// </summary>
        /// <returns></returns>
        public async Task<List<ProcecureSubTreeDTO>> GetTreeSubListAsyncs()
        {
            //var list = await Db.GetDataTableWithSqlAsync("select GROUP_CONCAT(Id) AS ProcecureIds from (select Id from PB_ProcecureSub ) as t ");
            //if (list.Rows[0][0].ToString().Length > 0)
            //{
                //var ProcecureIds = string.Join(",", list.Rows[0]["ProcecureIds"].ToString().Split(',').Where(x => !string.IsNullOrEmpty(x)).Select(x => "'" + x + "'"));

                //string sql = $@"select * from PB_ProcecureMain where Id in (SELECT ParentId FROM PB_ProcecureSub where id in ({ ProcecureIds }) group by ParentId)";

                //var subdata = await Db.GetListBySqlAsync<PB_ProcecureSub>(sql);
                var subdata = await Db.GetIQueryable<PB_ProcecureMain>().ToListAsync();
                List<ProcecureSubTreeDTO> listData = new List<ProcecureSubTreeDTO>();
                foreach (var item in subdata)
                {
                    ProcecureSubTreeDTO tree = new ProcecureSubTreeDTO();
                    tree.Id = item.Id;
                    tree.ParentId = "";
                    tree.Text = item.Name;
                    tree.Value = item.Id;
                    listData.Add(tree);
                    var childrenItem = await Db.GetIQueryable<PB_ProcecureSub>().Where(x => x.ParentId == item.Id).ToListAsync();
                    foreach (var items in childrenItem)
                    {
                        ProcecureSubTreeDTO treedata = new ProcecureSubTreeDTO();
                        treedata.Id = items.Id;
                        treedata.ParentId = items.ParentId;
                        treedata.Text = items.Name;
                        treedata.Value = items.Id;
                        listData.Add(treedata);
                    }
                }
                var treeList = listData
                   .Select(x => new ProcecureSubTreeDTO
                   {
                       Id = x.Id,
                       ParentId = x.ParentId,
                       Text = x.Text,
                       Value = x.Id,
                   }).ToList();

                return TreeHelper.BuildTree(treeList);
            //}
            //else
            //{
            //    throw new BusException("暂无数据");
            //}
        }

        /// <summary>
        /// 派工获取项目信息
        /// </summary>
        /// <returns></returns>
        public async Task<List<PB_ProjectDataDTO>> GetProjectAsync()
        {
            var list = await Db.GetDataTableWithSqlAsync("SELECT d.Id,  d.CODE,  d.NAME FROM   PB_ProductionOrderDetail AS a  JOIN PB_ProductionOrder AS b ON a.ParentId = b.Id    JOIN PB_DesignList AS c ON b.DesignId = c.Id JOIN PB_Project AS d ON c.ProjectId = d.Id WHERE  a.CompleteNums < Productionquantity GROUP BY d.Id, d.CODE, d.NAME");
            List<PB_ProjectDataDTO> listdata = new List<PB_ProjectDataDTO>();
            foreach (DataRow dr in list.Rows)
            {
                PB_ProjectDataDTO pbp = new PB_ProjectDataDTO();
                pbp.Id = dr["Id"].ToString();
                pbp.Name = dr["NAME"].ToString();
                pbp.Code= dr["CODE"].ToString();
                listdata.Add(pbp);
            }
            return listdata;
        }

        /// <summary>
        /// 派工获取工件信息
        /// </summary>
        /// <param name="projectId">项目Id</param>
        /// <param name="subId">工序Id</param>
        /// <param name="input">筛选条件</param>
        /// <returns></returns>
        public async Task<PageResult<WorkpieceDTO>> GetMaterialAsync(string projectId, string subId, PageInput<PBMaterialConditionDTO> input)
        {
            string strwhere = "";
            if (!input.Search.OrderId.IsNullOrEmpty())
            {
                strwhere = @" and a.ParentId='" + input.Search.OrderId + "' ";
            }
            string strsql = string.Empty;
            if (!projectId.IsNullOrEmpty())
            {
                strsql = @"  select e.Id,e.Code,e.Name,e.SimpleName,e.Spec,e.Weight,e.Ofmaterial,a.ParentId as orderId,b.POName,a.Id as orderdetailId,e.MaterialTypeId,b.IsRefurbishment from PB_ProductionOrderDetail as a join PB_ProductionOrder as b on a.ParentId=b.Id left join PB_DesignList as c on b.DesignId=c.Id left join PB_Project as d on c.ProjectId=d.Id left join View_PB_Material as e on a.MaterialId=e.Id  where a.CompleteNums<a.Productionquantity  and e.ProcecureId like '%" + subId + "%' and d.Id='" + projectId + "' "+ strwhere + "  group by e.Id,e.Code,e.Name,e.SimpleName,e.Spec,e.Weight,e.Ofmaterial,a.ParentId,b.POName,a.Id,e.MaterialTypeId,b.IsRefurbishment ";
            }
            else
            {
                strsql = @" select e.Id,e.Code,e.Name,e.SimpleName,e.Spec,e.Weight,e.Ofmaterial,a.ParentId as orderId,b.POName,a.Id as orderdetailId,e.MaterialTypeId,b.IsRefurbishment from PB_ProductionOrderDetail as a join PB_ProductionOrder as b on a.ParentId=b.Id left join View_PB_Material as e on a.MaterialId=e.Id  where a.CompleteNums< a.Productionquantity and IFNULL(b.DesignId,'') = ''  " + strwhere + " and e.ProcecureId like '%" + subId + "%'  group by e.Id,e.Code,e.Name,e.SimpleName,e.Spec,e.Weight,e.Ofmaterial,a.ParentId,b.POName,a.Id,e.MaterialTypeId,b.IsRefurbishment  ";
            }
            var search = input.Search;
            string where = string.Empty;
            if (!search.Keyword.IsNullOrEmpty())
            {
                where += @" and (Code like '%" + search.Keyword + "%' or Name like '%" + search.Keyword + "%' or POName like '%" + search.Keyword + "%')";
            }
            if (!search.MaterialTypeId.IsNullOrEmpty())
            {
                where += @" and MaterialTypeId='" + search.MaterialTypeId + "'";
            }
            string sqlMain = @"select *,ROW_NUMBER() over(order by orderId desc) rn from ( " + strsql + "  UNION  SELECT Id,Code,Name,SimpleName,Spec,Weight,Ofmaterial,'' as orderId,'' as POName,'' as orderdetailId,MaterialTypeId,'2' as IsRefurbishment  FROM PB_Material WHERE (IsMine=1 OR IsLabour=1) )as t where 1=1 " + where + " ";
            var listdata = await Db.GetListBySqlAsync<WorkpieceDTO>(sqlMain);
            return listdata.GetPageResult(input);
        }
        private static string workorderNoValue = "";
        /// 获取派工单号
        /// </summary>
        /// <returns></returns>
        public string GetWorkOrderNoAsync(RedisOptions _redisOptions)
        {
            string workorderNo = "";
            //实例化Redis操作类
            _redisOptions.DbChannel = (int)Redis_Type.redis6;
            RedisSetHelper redis = new RedisSetHelper(_redisOptions);
            if (RedisSetHelper.Exists("DispatchNumber"))
            {
                string thedate = DateTime.Now.ToString("yyyy-MM-dd");
                if (redis.GetString("DispatchTime") == thedate)
                {
                    redis.SetString("DispatchNumber", (Convert.ToInt32(redis.GetString("DispatchNumber")) + 1).ToString());
                    workorderNo = "D_" + DateTime.Now.ToString("yyyyMMdd") + "_" + redis.GetString("DispatchNumber").PadLeft(4, '0');
                }
                else
                {
                    redis.SetString("DispatchTime", thedate);
                    redis.SetString("DispatchNumber", "1");
                    workorderNo = "D_" + DateTime.Now.ToString("yyyyMMdd") + "_" + redis.GetString("DispatchNumber").PadLeft(4, '0');
                }
            }
            else
            {
                redis.SetString("DispatchNumber", "0");
                redis.SetString("DispatchNumber", (Convert.ToInt32(redis.GetString("DispatchNumber")) + 1).ToString());
                workorderNo = "D_" + DateTime.Now.ToString("yyyyMMdd") + "_" + redis.GetString("DispatchNumber").PadLeft(4, '0');
            }
            workorderNoValue = workorderNo;
            return workorderNo;
        }

        [DataAddLog(UserLogType.生产派工, "Id", "派工单号")]
        /// <summary>
        /// 保存生产派工及派工详情
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public async Task AddData(PB_DispatchListDTO data)
        {
            var userdata =await Db.GetIQueryable<Base_User>().Where(m=>m.Id==_operator.UserId).FirstOrDefaultAsync();

            //获取任务单信息
            List<string> OrderIds = new List<string>();
            var ordergroup = data.DispatchDetail.GroupBy(x => new { x.OrderId }).Select(x => new { OrderId = x.Key.OrderId }).ToList();
            foreach (var item in ordergroup)
            {
                OrderIds.Add(item.OrderId);
            }
            List<PB_ProductionOrder> ProductionOrder = await Db.GetIQueryable<PB_ProductionOrder>().Where(x => OrderIds.Contains(x.Id) && x.IsRefurbishment == 4).ToListAsync();
            if (ProductionOrder.Count > 0)
            {
                OrderIds = new List<string>();
                foreach (var item in ProductionOrder)
                {
                    OrderIds.Add(item.Id);
                }
                //获取任务单明细信息
                List<PB_ProductionOrderDetail> ProductionOrderDetail = await Db.GetIQueryable<PB_ProductionOrderDetail>().Where(x => OrderIds.Contains(x.ParentId)).ToListAsync();
                //获取已派工数量
                List<PB_DispatchDetail> DispatchDetailList = await Db.GetIQueryable<PB_DispatchDetail>().Where(x => OrderIds.Contains(x.OrderId)).ToListAsync();
                var DetailGroup = DispatchDetailList.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, Nums = x.Sum(i => i.Nums) }).ToList();

                foreach (var item in ProductionOrderDetail)
                {
                    //已派工数量
                    var itemdata = DetailGroup.Where(x => x.MaterialId == item.MaterialId).FirstOrDefault();
                    var ditemdata = data.DispatchDetail.Where(x => x.MaterialId == item.MaterialId).FirstOrDefault();
                    if (!ditemdata.IsNullOrEmpty())
                    {
                        PB_Material Material = await Db.GetIQueryable<PB_Material>().Where(x => x.Id == item.MaterialId).FirstOrDefaultAsync();
                        if (ditemdata.Nums > (item.Productionquantity - (itemdata.IsNullOrEmpty() ? 0 : itemdata.Nums)))
                        {
                            throw new BusException($"{Material.Name}派工数量大于生产任务单未派工数量");
                        }
                    }

                }
            }
            PB_Dispatch pB_Schedule = new PB_Dispatch
            {
                Id = data.Id,//编号
                StaffId = data.StaffId,//员工id
                StaffName = data.StaffName,//员工姓名
                WorkOrderNo = data.WorkOrderNo,
                DeptId = userdata.DepartmentId,//组织id
                TeamType = data.TeamType,//班别
                DispatchDate = data.DispatchDate,//派工日期
                CreatorId = data.CreatorId,//创建人
                CreatorRealName = data.CreatorRealName,//创建人姓名
                CreateTime = data.CreateTime,//创建时间
                Deleted = data.Deleted,//是否删除
                State = 0,
                QrCode = h_Dispatch+","+data.Id,
            };
            await Db.InsertAsync(pB_Schedule);
            var PB_DispatchDetail = data.DispatchDetail.Select(x => new PB_DispatchDetail
            {
                Id = IdHelper.GetId(),//id
                ParentId = data.Id,//主表id
                IsRefurbishment = x.IsRefurbishment,//是否翻新
                SubId = x.SubId,//工序id
                ProjectId = x.ProjectId,//项目编号
                MaterialId = x.MaterialId,//物料编号
                OrderId = x.OrderId,
                OrderDetailId = x.OrderDetailId,
                Aperture = x.Aperture,//孔径
                Texture = x.Texture,//材质
                Nums = x.Nums,//数量
                CreatorId = data.CreatorId,//创建人id
                CreatorRealName = data.CreatorRealName,//创建人
                CreateTime = DateTime.Now,//创建时间
                Deleted = x.Deleted,//是否删除
                WorkReportQuantity = 0,
                SubordinateProduct = x.SubordinateProduct,
                MaterialLength = x.MaterialLength,
                QrCode = h_DispatchDetail + "," + x.Id,
                ModeType = x.ModeType
            }).ToList();
            foreach (var item in PB_DispatchDetail)
            {
                item.QrCode = h_DispatchDetail + "," + item.Id;
           
                //string sqlstr = @"UPDATE PB_ProductionOrderDetail SET Productionquantity=Productionquantity-" + item.Nums + "   WHERE id='" + item.OrderDetailId + "' and ParentId='" + item.OrderId + "'";
                //await ExecuteSqlAsync(sqlstr);
            }
            await Db.InsertAsync(PB_DispatchDetail);
           
        
        }

        /// <summary>
        /// 派工打印
        /// </summary>
        /// <param name="qrcode">二维码</param>
        /// <returns></returns>
        public async Task<List<PB_DispatchDataDTO>> GetDispatchListDataPrintAsync(string qrcode)
        {
            string[] strqrcode = qrcode.Split(',');
            List<PB_DispatchDataDTO> listdata = new List<PB_DispatchDataDTO>();
            string datestr = DateTime.Now.ToString("yyyy-MM-dd");
            //string datestr = "2021-12-07";
            if (strqrcode[0].ToString() == "01")
            {
                string sqlMain = @"select a.Id,a.StaffId,a.StaffName,a.WorkOrderNo,a.DeptId,c.`Name` as DeptName,a.TeamType,b.`Name` as TeamTypeName,a.DispatchDate from PB_Dispatch as a join PB_ShiftType as b on a.TeamType=b.Id join Base_Department as c on a.DeptId=c.Id where a.StaffId='" + strqrcode[1].ToString() + "' and a.DispatchDate='" + datestr + "'";
                var maindata = await Db.GetDataTableWithSqlAsync(sqlMain);
                foreach (DataRow dr in maindata.Rows)
                {
                    PB_DispatchDataDTO pbdata = new PB_DispatchDataDTO();
                    pbdata.Id = dr["Id"].ToString();
                    pbdata.StaffId = dr["StaffId"].ToString();
                    pbdata.StaffName = dr["StaffName"].ToString();
                    pbdata.WorkOrderNo = dr["WorkOrderNo"].ToString();
                    pbdata.DeptId = dr["DeptId"].ToString();
                    pbdata.DeptName = dr["DeptName"].ToString();
                    pbdata.TeamType = dr["TeamType"].ToString();
                    pbdata.TeamTypeName = dr["TeamTypeName"].ToString();
                    pbdata.DispatchDate = Convert.ToDateTime(dr["DispatchDate"]);
                    Expression<Func<PB_DispatchDetail, PB_Material, PB_DispatchDetailDTO>> selects = (a, b) => new PB_DispatchDetailDTO
                    {
                        MaterialIdName = b.Name
                    };
                    selects = selects.BuildExtendSelectExpre();
                    var qdata = from a in Db.GetIQueryable<PB_DispatchDetail>().AsExpandable()
                                join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
                                from b in ab.DefaultIfEmpty()
                                select @selects.Invoke(a, b);
                    qdata = qdata.Where(x => x.ParentId == dr["Id"].ToString());
                    pbdata.DispatchDetail = await qdata.ToListAsync();
                    listdata.Add(pbdata);
                }
            }
            else if (strqrcode[0].ToString() == "05")
            {
                string sqlMain = @"select a.Id,a.StaffId,a.StaffName,a.WorkOrderNo,a.DeptId,c.`Name` as DeptName,a.TeamType,b.`Name` as TeamTypeName,a.DispatchDate from PB_Dispatch as a join PB_ShiftType as b on a.TeamType=b.Id join Base_Department as c on a.DeptId=c.Id where a.DeptId='" + strqrcode[1].ToString() + "' and a.DispatchDate='" + datestr + "'";
                var maindata = await Db.GetDataTableWithSqlAsync(sqlMain);
                foreach (DataRow dr in maindata.Rows)
                {
                    PB_DispatchDataDTO pbdata = new PB_DispatchDataDTO();
                    pbdata.Id = dr["Id"].ToString();
                    pbdata.StaffId = dr["StaffId"].ToString();
                    pbdata.StaffName = dr["StaffName"].ToString();
                    pbdata.WorkOrderNo = dr["WorkOrderNo"].ToString();
                    pbdata.DeptId = dr["DeptId"].ToString();
                    pbdata.DeptName = dr["DeptName"].ToString();
                    pbdata.TeamType = dr["TeamType"].ToString();
                    pbdata.TeamTypeName = dr["TeamTypeName"].ToString();
                    pbdata.DispatchDate = Convert.ToDateTime(dr["DispatchDate"]);
                    Expression<Func<PB_DispatchDetail, PB_Material, PB_DispatchDetailDTO>> selects = (a, b) => new PB_DispatchDetailDTO
                    {
                        MaterialIdName = b.Name
                    };
                    selects = selects.BuildExtendSelectExpre();
                    var qdata = from a in Db.GetIQueryable<PB_DispatchDetail>().AsExpandable()
                                join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
                                from b in ab.DefaultIfEmpty()
                                select @selects.Invoke(a, b);
                    qdata = qdata.Where(x => x.ParentId == dr["Id"].ToString());
                    pbdata.DispatchDetail = await qdata.ToListAsync();
                    listdata.Add(pbdata);
                }
            }
            return listdata;
        }

        public async Task<List<PB_DispatchDTO>> Selectid(string id)
        {
            Expression<Func<PB_Dispatch, Base_Department, PB_ShiftType, PB_DispatchDTO>> select = (a, b, c) => new PB_DispatchDTO
            {
                DepartmentName = b.Name,
                TeamTypeName = c.Name
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in GetIQueryable().AsExpandable()
                    join b in Db.GetIQueryable<Base_Department>() on a.DeptId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<PB_ShiftType>() on a.TeamType equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    select @select.Invoke(a, b, c);
            string[] arr = id.Split(',');
            List<string> List = new List<string>();
            foreach (var item in arr)
            {
                List.Add(item);
            }
            q = q.Where(m => List.Contains(m.Id));
            var res = await q.ToListAsync();
            return res;
        }

        public async Task<List<PB_DispatchDetailDTO>> SelectParint(string parint)
        {
            Expression<Func<PB_DispatchDetail, PB_Material, PB_Project, PB_ProductionOrder,PB_ProcecureSub , PB_DispatchDetailDTO>> select = (a, b, c, d,e) => new PB_DispatchDetailDTO
            {
                MaterialIdName = b.Name,
                MateralldCode = b.QrCode,
                ProName = c.Name,
                ProductionOrderName = d.POName,
                BatchNumber = d.BatchNumber,
                Code = d.Code,
                SubName=e.Name,
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<PB_DispatchDetail>().AsExpandable()
                    join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<PB_Project>() on a.ProjectId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<PB_ProductionOrder>() on a.OrderId equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                           join e in Db.GetIQueryable<PB_ProcecureSub>() on a.SubId equals e.Id into ae
                    from e in ae.DefaultIfEmpty()
                    select @select.Invoke(a, b, c, d,e);
            q = q.Where(m => m.ParentId == parint);
            var res = await q.ToListAsync();
            return res;
        }

        public async Task<List<PB_DispatchDetailDTO>> SelectParints(string parints)

        {
            Expression<Func<PB_DispatchDetail, PB_Material,PB_Project, PB_ProductionOrder, PB_ProcecureSub, PB_DispatchDetailDTO>> select = (a, b,c,d,e) => new PB_DispatchDetailDTO
            {
                MaterialIdName = b.Name,
                MateralldCode = b.QrCode,
                ProName=c.Name,
                ProductionOrderName=d.POName,
                BatchNumber=d.BatchNumber,
                Code=d.Code,
                SubName=e.Name,
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<PB_DispatchDetail>().AsExpandable()
                    join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<PB_Project>() on a.ProjectId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<PB_ProductionOrder>() on a.OrderId equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    join e in Db.GetIQueryable<PB_ProcecureSub>() on a.SubId equals e.Id into ae
                    from e in ae.DefaultIfEmpty()
                    select @select.Invoke(a, b,c,d,e);
            string[] arr = parints.Split(',');
            List<string> List = new List<string>();
            foreach (var item in arr)
            {
                List.Add(item);
            }
            q = q.Where(m => List.Contains(m.ParentId));
            var res = await q.ToListAsync();
            return res;
        }

        public async Task<List<PB_Material>> GetMaterialDataAsync(string MaterialId)
        {
            var q = await Db.GetIQueryable<PB_Material>().Where(x => x.Id == MaterialId).ToListAsync();
            return q;
        }

        /// <summary>
        /// 获取派工的任务单
        /// </summary>
        /// <param name="projectId">项目Id</param>
        /// <returns></returns>
        public async Task<List<PB_ProductionOrder>> GetProductionOrderListDataAsync(string projectId, string POName, DateTime? StateTime, DateTime? EndTime)
        {
            List<PB_ProductionOrder> listdata = new List<PB_ProductionOrder>();

            if (projectId.IsNullOrEmpty())
            {
                string sqlMain = "select* from PB_ProductionOrder as a join PB_DesignList as b on a.DesignId = b.Id  inner join PB_Project as p ON b.ProjectId=p.Id   WHERE a.`Status`=1  and  a.State=true {0} {1} ";
                string poname = "";
                if (!POName.IsNullOrEmpty()) {
                    poname = $"AND  a.POName LIKE '%{POName}%' ";
                }
                string Time = "";
                if (!StateTime.IsNullOrEmpty() && !EndTime.IsNullOrEmpty()) {
                    Time = $"AND a.BeginDate between    '" + Convert.ToDateTime(StateTime).ToString("yyyy-MM-dd 00:00:00") + "' and '" + Convert.ToDateTime(EndTime).ToString("yyyy-MM-dd 23:59:59") + " '";
                }
                sqlMain = string.Format(sqlMain, poname, Time);
                listdata = await Db.GetListBySqlAsync<PB_ProductionOrder>(sqlMain);
            }
            else if (!projectId.IsNullOrEmpty())
            {
                string sqlMain = $@"select* from PB_ProductionOrder as a join PB_DesignList as b on a.DesignId = b.Id where b.ProjectId = '" + projectId + "' and  a.Status=1  and   State=true  {0} {1}";
                listdata = await Db.GetListBySqlAsync<PB_ProductionOrder>(sqlMain);
                string poname = "";
                if (!POName.IsNullOrEmpty())
                {
                    poname = $"AND  a.POName LIKE '%{POName}%' ";
                }
                string Time = "";
                if (!StateTime.IsNullOrEmpty() && !EndTime.IsNullOrEmpty())
                {
                    Time = $"AND a.BeginDate between    '" + Convert.ToDateTime(StateTime).ToString("yyyy-MM-dd 00:00:00") + "' and '" + Convert.ToDateTime(EndTime).ToString("yyyy-MM-dd 23:59:59") + " '";
                }
                sqlMain = string.Format(sqlMain, poname, Time);
            }
            else
            {
                listdata = await Db.GetIQueryable<PB_ProductionOrder>().Where(x => x.State == false && x.Status == 1).ToListAsync();
            }
            return listdata;
        }

        public async Task <List<PB_DispatchDTO>> GetDataListInfo(List<string> ParentId)
         {

            Expression<Func<PB_Dispatch, Base_Department, PB_ShiftType, PB_DispatchDTO>> selects = (a, b, c) => new PB_DispatchDTO
            {
                DepartmentName = b.Name,
                TeamTypeName = c.Name
            };
            selects = selects.BuildExtendSelectExpre();
            var k = from a in GetIQueryable().AsExpandable()
                    join b in Db.GetIQueryable<Base_Department>() on a.DeptId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<PB_ShiftType>() on a.TeamType equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    select @selects.Invoke(a, b, c);

            List<PB_DispatchDTO> listdata = new List<PB_DispatchDTO>();
            var qdata = await k.Where(x=>ParentId.Contains(x.Id)).ToListAsync();
            foreach (var item in qdata)
            {
                Expression<Func<PB_DispatchDetail, PB_Material, PB_Project, PB_ProductionOrder, PB_ProcecureSub, PB_DesignList, PB_Buildingnumber, PB_Material, PB_DispatchDetailDTO>> select = (a, b, c, d, e, f, g, h) => new PB_DispatchDetailDTO
                {
                    MaterialIdName = b.Name,
                    MateralldCode = b.QrCode,
                    ProName = c.Name,
                    ProductionOrderName = d.POName,
                    BatchNumber = d.BatchNumber,
                    Code = d.Code,
                    SubName = e.Name,
                    MaterSpec = b.Spec,
                    DanZhong = b.Weight == null ? 0 : b.Weight,
                    Zongzhong = a.Nums * b.Weight == null ? 0 : a.Nums * b.Weight,
                    ProcessingRequirements = a.Aperture + a.Texture,
                    BuildNo = g.Buildingnumber,
                    SubordinateProductName = h.Name,
                    Drawing=b.MaterialImg,
                    Calculation=b.Remarks,
                     Calculation1 = b.Remarks1,
                    Calculation2 = b.Remarks2,
                    Calculation3 = b.Remarks3,
                    Calculation4 = b.Remarks4,
                    Calculation5 = b.Remarks5,
                    Calculation6 = b.Remarks6,
                    Calculation7 = b.Remarks7,
                    Calculation8 = b.Remarks8,
                    Calculation9 = b.Remarks9,
                    Calculation10 = b.Remarks10,

                };
                select = select.BuildExtendSelectExpre();
                var q = from a in Db.GetIQueryable<PB_DispatchDetail>().AsExpandable()
                        join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
                        from b in ab.DefaultIfEmpty()
                        join c in Db.GetIQueryable<PB_Project>() on a.ProjectId equals c.Id into ac
                        from c in ac.DefaultIfEmpty()
                        join d in Db.GetIQueryable<PB_ProductionOrder>() on a.OrderId equals d.Id into ad
                        from d in ad.DefaultIfEmpty()
                        join e in Db.GetIQueryable<PB_ProcecureSub>() on a.SubId equals e.Id into ae
                        from e in ae.DefaultIfEmpty()
                        join f in Db.GetIQueryable<PB_DesignList>() on d.DesignId equals f.Id into df
                        from f in df.DefaultIfEmpty()
                        join g in Db.GetIQueryable<PB_Buildingnumber>() on f.BuildingNumberID equals g.Id into fg
                        from g in fg.DefaultIfEmpty()
                        join h in Db.GetIQueryable<PB_Material>() on a.SubordinateProduct equals h.Id into ah
                        from h in ah.DefaultIfEmpty()
                        select @select.Invoke(a, b, c, d, e, f, g, h);
                q = q.Where(x => x.ParentId == item.Id);
                var res = await q.ToListAsync();

                #region  组转件物料信息
                List<string> materialids = new List<string>();
                foreach (var itemdata in res)
                {
                    if (itemdata.IsRefurbishment == 4)
                    {
                        materialids.Add(itemdata.MaterialId);
                    }
                }

                if (materialids.Count > 0)
                {
                    string sqlMain = @"select a.MaterailId as PmaterialId,b.Id,b.MaterailId as MaterialId,b.MaterialName,c.`Code` as MaterialCode,c.Spec as MaterialSpec,b.Quantity,CONCAT('04',',',b.MaterailId) as QrCode from Bom_MainNode as a
left join Bom_SubNode as b on a.Id=b.ParentId
left join PB_Material as c on b.MaterailId=c.Id
where a.MaterailId in ('{0}')";
                    string sql = string.Format(sqlMain, string.Join("','", materialids.ToArray()));
                    List<BomMaterialDetailDTO> BomMaterialDetail = await Db.GetListBySqlAsync<BomMaterialDetailDTO>(sql);

                    foreach (var mitem in res)
                    {
                        var itemdata = BomMaterialDetail.Where(x => x.PmaterialId == mitem.MaterialId).ToList();
                        if (itemdata != null)
                        {
                            mitem.BomMaterialDetail = itemdata;
                        }
                    }
                }

                #endregion
                PB_DispatchDTO pblist = new PB_DispatchDTO();
                pblist.Id = item.Id;
                pblist.DepartmentName = item.DepartmentName;
                pblist.QrCode = item.QrCode;
                pblist.TeamTypeName = item.TeamTypeName;
                pblist.StaffId = item.StaffId;
                pblist.StaffName = item.StaffName;
                pblist.WorkOrderNo = item.WorkOrderNo;
                pblist.DeptId = item.DeptId;
                pblist.TeamType = item.TeamType;
                pblist.DispatchDate = item.DispatchDate;
                pblist.CreatorId = item.CreatorId;
                pblist.CreatorRealName = item.CreatorRealName;
                pblist.CreateTime = item.CreateTime;
                pblist.Deleted = item.Deleted;
                pblist.subdata = res;
                listdata.Add(pblist);
            }
            if (listdata.Count > 0)
            {
                return listdata;
            }
            else
            {
                throw new BusException("暂无数据");
            }
        }
        /// <summary>
        /// 生产领料
        /// </summary>
        /// <param name="cateqrcode">派工单二维码</param>
        /// <returns></returns>
        public async Task<PB_DispatchDetailPDADTO> GetPDADataListInfo(string cateqrcode)
        {
            Base_User bdata = await Db.GetIQueryable<Base_User>().Where(x => x.Id == _operator.UserId).FirstOrDefaultAsync();
            string[] qrcodemodel = cateqrcode.Split(',');
            string juber = qrcodemodel[0].ToString();
            if (juber == "16")
            {
                //List<PB_DispatchDetailPDADTO> list = new List<PB_DispatchDetailPDADTO>();
                Expression<Func<PB_DispatchDetail, PB_Material, PB_Project, PB_ProductionOrder, PB_ProcecureSub,PB_Dispatch, PB_DispatchDetailPDADTO>> select = (a, b, c, d, e,f) => new PB_DispatchDetailPDADTO
                {
                    MaterialIdName = b.Name,
                    MateralldCode = b.QrCode,
                    ProName = c.Name,
                    ProductionOrderName = d.POName,
                    BatchNumber = d.BatchNumber,
                    Code = d.Code,
                    SubName = e.Name,
                    StaffId = f.StaffId,
                    StaffName = f.StaffName
                };
                select = select.BuildExtendSelectExpre();
                var q = from a in Db.GetIQueryable<PB_DispatchDetail>().AsExpandable()
                        join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
                        from b in ab.DefaultIfEmpty()
                        join c in Db.GetIQueryable<PB_Project>() on a.ProjectId equals c.Id into ac
                        from c in ac.DefaultIfEmpty()
                        join d in Db.GetIQueryable<PB_ProductionOrder>() on a.OrderId equals d.Id into ad
                        from d in ad.DefaultIfEmpty()
                        join e in Db.GetIQueryable<PB_ProcecureSub>() on a.SubId equals e.Id into ae
                        from e in ae.DefaultIfEmpty()
                        join f in Db.GetIQueryable<PB_Dispatch>() on a.ParentId equals f.Id into af
                        from f in af.DefaultIfEmpty()
                        select @select.Invoke(a, b, c, d, e,f);

                PB_DispatchDetailPDADTO list =  await q.Where(x => x.QrCode == cateqrcode).FirstOrDefaultAsync();


                if (bdata.RealName != list.StaffName)
                {
                     throw new BusException("领取人与派工单姓名不符!");
                 }
                else
                {
                    return list;
                }
            }
            else
            {
                throw new BusException("请扫描或输入正确的生产派工单二维码信息");
            }
        }


        /// <summary>
        /// 获取生产任务单对应的包明细
        /// </summary>
        /// <param name="stockupQrCode">备货单二维码</param>
        /// <returns></returns>
        /// PB_DispatchLoadList
        public async Task<List<PB_DispatchLoadListDataDTO>> GetStockUpDetailListDataAsync(string stockupQrCode)
        {
            string stockupid = stockupQrCode.Split(',')?[1].ToString();
            string sqlMain = @" select* from(select b.PackId, b.MaterialId, c.BoxNo, c.BoxName, (concat(e.`Name`,'/', f.`Name`,'/', d.`Name`)) as PlaceName,g.Spec as MaterialSpec,b.Nums as StockNumber,b.CreateTime,c.QrCode from PB_DispatchDetail as a join PB_DispatchLoadList as b on a.Id=b.ParentId join PB_PackList as c on b.PackId=c.Id join PB_Location as d on b.LocalId=d.Id join PB_StorArea as e on d.AreaId=e.Id join PB_Rack as f on d.RackId=f.Id left join PB_Material as g on b.MaterialId=g.Id where a.Id='" + stockupid + "' AND b.State <> 2 and c.PackageType=0     UNION ALL    select PackId,'' as MaterialId, BoxNo, BoxName, PlaceName,'' as MaterialSpec, SUM(StockNumber) as StockNumber, CreateTime, QrCode from (select b.PackId, b.MaterialId, c.BoxNo, c.BoxName, (concat(e.`Name`,'/', f.`Name`,'/', d.`Name`)) as PlaceName,g.Spec as MaterialSpec,b.Nums as StockNumber,DATE_FORMAT(b.CreateTime,'%Y-%m-%d') as CreateTime,c.QrCode from PB_DispatchDetail as a join PB_DispatchLoadList as b on a.Id=b.ParentId join PB_PackList as c on b.PackId=c.Id join PB_Location as d on b.LocalId=d.Id join PB_StorArea as e on d.AreaId=e.Id join PB_Rack as f on d.RackId=f.Id left join PB_Material as g on b.MaterialId=g.Id where a.Id='" + stockupid + "' AND b.State <> 2 and c.PackageType=1 )as t group by PackId, BoxNo, BoxName, PlaceName, CreateTime, QrCode     UNION all   select b.PackId, b.MaterialId, c.Id as BoxNo, c.Name as BoxName, (concat(e.`Name`,'/', f.`Name`,'/', d.`Name`)) as PlaceName,c.Spec as MaterialSpec,b.Nums as StockNumber,b.CreateTime,concat('04,', b.MaterialId) as QrCode from PB_DispatchDetail as a join PB_DispatchLoadList as b on a.Id=b.ParentId join PB_Material as c on b.MaterialId=c.Id join PB_Location as d on b.LocalId=d.Id join PB_StorArea as e on d.AreaId=e.Id join PB_Rack as f on d.RackId=f.Id where a.Id='" + stockupid + "' AND b.State <> 2 and b.PackId='' ) as t order by CreateTime desc ";
            return await Db.GetListBySqlAsync<PB_DispatchLoadListDataDTO>(sqlMain);
        }


        

        public async Task<PB_DispatchDetailPDANumberDTO> GetPDADataMaterialListInfo(string localQrCode, string packqrcode)
        {
            var packmodel = packqrcode.Split(',')?[0];
            string packid = string.Empty;
            if (packmodel == "04")
            {
                var model = packqrcode.Split(',');
                if (model.Length > 2)
                {
                    if (model.Length < 4)
                    {
                        throw new BusException("请扫描物料二维码或手动输入");
                    }
                    else
                    {
                        packid = model?[3].ToString();
                    }
                }
                else
                {
                    packid = model?[1].ToString();
                }
            }
            PB_DispatchDetailPDANumberDTO list = new PB_DispatchDetailPDANumberDTO();
            Expression<Func<PB_Material, IT_LocalMaterial,PB_Location, PB_Rack,PB_StorArea, PB_DispatchDetailPDANumberDTO>> select = (a, b,c,d,e) => new PB_DispatchDetailPDANumberDTO
            {
                LocalId =b.LocalId,
                PlaceName = e.Name +"/"+d.Name+"/"+c.Name
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<PB_Material>().AsExpandable()
                    join b in Db.GetIQueryable<IT_LocalMaterial>() on a.Id equals b.MaterialId into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<PB_Location>() on b.LocalId equals c.Id  into bc
                    from c in bc.DefaultIfEmpty()
                    join d in Db.GetIQueryable<PB_Rack>() on b.RackId equals d.Id into bd
                    from d in bd.DefaultIfEmpty()
                    join e in Db.GetIQueryable<PB_StorArea>() on b.AreaId equals e.Id into be
                    from e in be.DefaultIfEmpty()
                    select @select.Invoke(a, b,c,d,e);
            
            list =  await q.Where(x => x.Id == packid && x.LocalId == localQrCode.Split(',')[1].ToString() && x.IsProduct == false).FirstOrDefaultAsync();
            if (list == null)
                throw new BusException("请扫描或输入正确的非成品物料二维码");
            else
                return list;
        }

        public async Task<bool> GetLocationProjectTruOrFalseAsync(string packqrcode, string cateqrcode, string localQrCode)
        {
            bool bl = true;
            //包Id
            //包编号/物料编号
            string packId = string.Empty;
            string materialId = string.Empty;
            string packmodel = packqrcode.Split(',')?[0].ToString();
            //货位
            string localId = localQrCode.Split(',')?[1].ToString();
            if (packmodel == "13")
            {
                packId = packqrcode.Split(',')?[1].ToString();
            }
            else if (packmodel == "04")
            {
                var model = packqrcode.Split(',');
                if (model.Length > 2)
                {
                    materialId = model?[3].ToString();
                }
                else
                {
                    materialId = model?[1].ToString();
                }
            }
            //派工单Id
            string pickupId = cateqrcode.Split(',')?[1].ToString();
            var pickdata = await Db.GetIQueryable<PB_DispatchDetail>().Where(x => x.Id == pickupId).FirstOrDefaultAsync();
            if (packmodel == "13")
            {
                //查询包在仓库对应的货位的信息
                Expression<Func<PB_Location, IT_LocalDetail, PB_LocationProjectDTO>> select = (a, b) => new PB_LocationProjectDTO
                {
                    PackId = b.PackId
                };
                select = select.BuildExtendSelectExpre();
                var q = from a in Db.GetIQueryable<PB_Location>().AsExpandable()
                        join b in Db.GetIQueryable<IT_LocalDetail>() on a.Id equals b.LocalId into ab
                        from b in ab.DefaultIfEmpty()
                        select @select.Invoke(a, b);
                var locadata = await q.Where(x => x.PackId == packId).FirstOrDefaultAsync();

                if (locadata != null)
                {
                    //货位是否有项目信息
                    if (locadata.ProjectId.IsNullOrEmpty())
                    {
                        bl = true;
                    }
                    else
                    {
                        if (locadata.ProjectId == pickdata.ProjectId)
                        {
                            bl = true;
                        }
                        else
                        {
                            bl = false;
                        }
                    }
                }
            }
            else
            {
                PB_DispatchDetail pbdata = await Db.GetIQueryable<PB_DispatchDetail>().Where(x => x.Id == pickupId && x.MaterialId == materialId).FirstOrDefaultAsync();
                if (pbdata != null)
                {
                    PB_Location locadata = await Db.GetIQueryable<PB_Location>().Where(x => x.Id == localId).FirstOrDefaultAsync();
                    if (locadata != null)
                    {
                        //货位是否有项目信息
                        if (locadata.ProjectId.IsNullOrEmpty())
                        {
                            bl = true;
                        }
                        else
                        {
                            if (locadata.ProjectId == pickdata.ProjectId)
                            {
                                bl = true;
                            }
                            else
                            {
                                bl = false;
                            }
                        }
                    }
                }
            }
            return bl;
        }
        [Transactional]
        public async Task UpdateStockUpDataAsync(DispatchDTO data)
        {
            //包编号/物料编号
            string packid = string.Empty;
            string materialId = string.Empty;
            string packmodel = string.Empty;
             packmodel = data.packqrcode.Split(',')?[0].ToString();
            if (packmodel == "13")
            {
                packid = data.packqrcode.Split(',')?[1].ToString();
            }
            else if (packmodel == "04")
            {
                var model = data.packqrcode.Split(',');
                if (model.Length > 2)
                {
                    materialId = model?[3].ToString();
                }
                else
                {
                    materialId = model?[1].ToString();
                }
            }
            Base_User bdata = await Db.GetIQueryable<Base_User>().Where(x => x.Id == _operator.UserId).FirstOrDefaultAsync();
            //派工单编号
            string stockupQrCode = data.cateqrcode.Split(',')?[1].ToString();

            //货位编号
            string locaId = data.locaqrcode.Split(',')?[1].ToString();

            if (packmodel == "13")
            {
                PB_PackList PB_PackListdata = await Db.GetIQueryable<PB_PackList>().Where(x => x.Id == packid).FirstOrDefaultAsync();
                if (PB_PackListdata == null)
                {
                    throw new BusException("请扫描正确的包二维码!");
                }
                if (PB_PackListdata.PackageType == 0)
                {
                    //获取仓库要出库的包信息
                    Expression<Func<IT_LocalDetail, PB_PackList, StockPackListDTO>> packselect = (a, b) => new StockPackListDTO
                    {
                        BoxName = b.BoxName,
                        PackageType = b.PackageType
                    };
                    packselect = packselect.BuildExtendSelectExpre();
                    var packdata = from a in Db.GetIQueryable<IT_LocalDetail>().AsExpandable()
                                   join b in Db.GetIQueryable<PB_PackList>() on a.PackId equals b.Id into ab
                                   from b in ab.DefaultIfEmpty()
                                   select @packselect.Invoke(a, b);
                    var pack = await packdata.Where(x => x.PackId == packid && x.LocalId == locaId).FirstOrDefaultAsync();
                    if (pack != null)
                    {
                        var trueorfalse = await _check.GetTrueOrFalse(pack.MaterialId, pack.StorId, locaId);
                        if (trueorfalse.boolType == false)
                        {
                            throw new BusException("仓库物料正在盘点中禁止下架出库");
                        }

                        IT_LocalMaterial materdata = await Db.GetIQueryable<IT_LocalMaterial>().Where(x => x.MaterialId == pack.MaterialId && x.StorId == pack.StorId && x.LocalId == pack.LocalId).FirstOrDefaultAsync();
                        if (materdata == null)
                        {
                            throw new BusException("请检查库存物料是否存在");
                        }
                        else
                        {
                            double? dnum = materdata.Nums_O + materdata.Nums_N;
                            if (pack.Num > dnum)
                            {
                                throw new BusException("请检查库存物料数量是否满足!");
                            }
                        }

                        //获取生产领料单明细信息
                        //var detaildata = await Db.GetIQueryable<PB_PickUpList>().Where(x => x.ParentId == stockupQrCode && x.MaterialId == pack.MaterialId).FirstOrDefaultAsync();
                        var detaildata = await Db.GetIQueryable<PB_DispatchDetail>().Where(x => x.Id == stockupQrCode).FirstOrDefaultAsync();
                        if (detaildata != null)
                        {
                            #region 生产领料需要走备货单
                            if (detaildata.ReadyNums == null)
                            {
                                detaildata.ReadyNums = 0;
                            }
                            //备货单剩余备货数量
                            double? surplusnum = detaildata.Nums - detaildata.ReadyNums;
                            //判断：包的物料数量大于备货单剩余备货数量
                            if (pack.Num > surplusnum)
                            {
                                throw new BusException("包的物料数量大于备货单物料的剩余备货数量");
                            }

                            //增加备货单明细日志
                            PB_DispatchLoadList pbdata = new PB_DispatchLoadList();
                            pbdata.Id = IdHelper.GetId();
                            pbdata.ParentId = detaildata.Id;
                            pbdata.LocalId = pack.LocalId;
                            pbdata.PackId = packid;
                            pbdata.MaterialId = detaildata.MaterialId;
                            pbdata.Nums = pack.Num;
                            pbdata.CreatorId = _operator.UserId;
                            pbdata.CreatorRealName = bdata.RealName;
                            pbdata.CreateTime = DateTime.Now;
                            pbdata.Deleted = false;
                            await Db.InsertAsync<PB_DispatchLoadList>(pbdata);
                            #endregion

                            //增加出库日志
                            PB_StockLog pblog = new PB_StockLog();
                            pblog.Id = IdHelper.GetId();
                            pblog.PackId = pack.PackId;
                            pblog.StorId = pack.StorId;
                            pblog.LocationId = pack.LocalId;
                            pblog.StockNumber = pack.Num;
                            pblog.CreatorId = _operator.UserId;
                            pblog.CreatorName = bdata.RealName;
                            pblog.OperatorId = _operator.UserId;
                            pblog.Type = 1;
                            pblog.OrderID = stockupQrCode;
                            pblog.CreateTime = DateTime.Now;
                            pblog.State = 1;
                            await Db.InsertAsync<PB_StockLog>(pblog);

                            //获取货位信息
                            var locationdata = await Db.GetIQueryable<PB_Location>().Where(x => x.Id == pack.LocalId).FirstOrDefaultAsync();

                            //删除库存
                            await ExecuteSqlAsync("delete from PB_Stock where PackId='" + pack.PackId + "'");

                            //删除库存明细表
                            await ExecuteSqlAsync("delete from IT_LocalDetail where PackId='" + pack.PackId + "'");

                            //修改货架上存放货物存汇总表的审核前数量和新旧数量
                            string itsql = string.Empty;
                            if (pack.OldOrNew == 0)
                            {
                                itsql = @"update IT_LocalMaterial set Nums_O=IFNULL(Nums_O,0)-" + pack.Num + ",ScanNum=IFNULL(ScanNum,0)-" + pack.Num + " where StorId='" + locationdata.StorId + "' and LocalId='" + locationdata.Id + "' and MaterialId='" + pack.MaterialId + "'";
                            }
                            else if (pack.OldOrNew == 1)
                            {
                                itsql = @"update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)-" + pack.Num + ",ScanNum=IFNULL(ScanNum,0)-" + pack.Num + " where StorId='" + locationdata.StorId + "' and LocalId='" + locationdata.Id + "' and MaterialId='" + pack.MaterialId + "'";
                            }
                            await ExecuteSqlAsync(itsql);

                            //修改物料审核前数量
                            string msql = @"update PB_Material set ScanNum=IFNULL(ScanNum,0)-" + pack.Num + " where Id='" + detaildata.MaterialId + "'";
                            await ExecuteSqlAsync(msql);

                            //修改派工单物料的已备货数量
                            await ExecuteSqlAsync("update PB_DispatchDetail set ReadyNums=IFNULL(ReadyNums,0)+" + pack.Num + " where Id='" + detaildata.Id + "'");

                            //修改包的状态为已出库
                            await ExecuteSqlAsync("update PB_PackList set Status=4,OutStorTime=NOW() where Id='" + pack.PackId + "'");
                        }
                        else
                        {
                            throw new BusException("请扫描正确的包信息");
                        }
                    }
                    else
                    {
                        throw new BusException("请扫描正确的货位上的包信息");
                    }
                }
                else if (PB_PackListdata.PackageType == 1)
                {
                    //获取仓库要出库的包信息
                    Expression<Func<IT_LocalDetail, PB_PackList, PB_DispatchDetail, StockPackListDTO>> packselect = (a, b, c) => new StockPackListDTO
                    {
                        BoxName = b.BoxName,
                        PackageType = b.PackageType,
                        PickUpListId = c.Id
                    };
                    packselect = packselect.BuildExtendSelectExpre();
                    var packdata = from a in Db.GetIQueryable<IT_LocalDetail>().AsExpandable()
                                   join b in Db.GetIQueryable<PB_PackList>() on a.PackId equals b.Id into ab
                                   from b in ab.DefaultIfEmpty()
                                   join c in Db.GetIQueryable<PB_DispatchDetail>() on a.MaterialId equals c.MaterialId into ac
                                   from c in ac.DefaultIfEmpty()
                                   select @packselect.Invoke(a, b, c);
                    var pack = await packdata.Where(x => x.PackId == packid && x.PickUpListId == stockupQrCode && x.LocalId == locaId).ToListAsync();
                    if (pack.Count > 0)
                    {
                        string strLocalId = string.Empty;
                        string strStorId = string.Empty;
                        foreach (var item in pack)
                        {
                            strLocalId = item.LocalId;
                            strStorId = item.StorId;
                            var trueorfalse = await _check.GetTrueOrFalse(item.MaterialId, item.StorId, locaId);
                            if (trueorfalse.boolType == false)
                            {
                                throw new BusException("仓库物料正在盘点中禁止下架出库");
                            }
                            IT_LocalMaterial materdata = await Db.GetIQueryable<IT_LocalMaterial>().Where(x => x.MaterialId == item.MaterialId && x.StorId == item.StorId && x.LocalId == item.LocalId).FirstOrDefaultAsync();
                            if (materdata == null)
                            {
                                throw new BusException("请检查库存物料是否存在");
                            }
                            else
                            {
                                double? dnum = materdata.Nums_O + materdata.Nums_N;
                                if (item.Num > dnum)
                                {
                                    throw new BusException("请检查库存物料数量是否满足!");
                                }
                            }
                            //获取货位信息
                            var locationdata = await Db.GetIQueryable<PB_Location>().Where(x => x.Id == item.LocalId).FirstOrDefaultAsync();

                            //修改货架上存放货物存汇总表的审核前数量和新旧数量
                            string itsql = string.Empty;
                            if (item.OldOrNew == 0)
                            {
                                itsql = @"update IT_LocalMaterial set Nums_O=IFNULL(Nums_O,0)-" + item.Num + ",ScanNum=IFNULL(ScanNum,0)-" + item.Num + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'";
                            }
                            else if (item.OldOrNew == 1)
                            {
                                itsql = @"update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)-" + item.Num + ",ScanNum=IFNULL(ScanNum,0)-" + item.Num + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'";
                            }
                            await ExecuteSqlAsync(itsql);

                            //修改物料审核前数量
                            string msql = @"update PB_Material set ScanNum=IFNULL(ScanNum,0)-" + item.Num + " where Id='" + item.MaterialId + "'";
                            await ExecuteSqlAsync(msql);

                            //修改备货单物料的已备货数量
                            await ExecuteSqlAsync("update PB_DispatchDetail set ReadyNums=IFNULL(ReadyNums,0)+" + item.Num + " where Id='" + stockupQrCode + "'; ");
                            ////增加备货单明细日志
                            PB_DispatchLoadList pbdata = new PB_DispatchLoadList();
                            pbdata.Id = IdHelper.GetId();
                            pbdata.ParentId = item.PickUpListId;
                            pbdata.LocalId = item.LocalId;
                            pbdata.PackId = packid;
                            pbdata.MaterialId = item.MaterialId;
                            pbdata.Nums = item.Num;
                            pbdata.CreatorId = _operator.UserId;
                            pbdata.CreatorRealName = bdata.RealName;
                            pbdata.CreateTime = DateTime.Now;
                            pbdata.Deleted = false;
                            await Db.InsertAsync<PB_DispatchLoadList>(pbdata);
                        }
                        //增加出库日志
                        PB_StockLog pblog = new PB_StockLog();
                        pblog.Id = IdHelper.GetId();
                        pblog.PackId = packid;
                        pblog.MaterialId = "";
                        pblog.StorId = strStorId;
                        pblog.LocationId = strLocalId;
                        pblog.StockNumber = PB_PackListdata.Nums;
                        pblog.CreatorId = _operator.UserId;
                        pblog.CreatorName = bdata.RealName;
                        pblog.OperatorId = _operator.UserId;
                        pblog.Type = 1;
                        pblog.OrderID = stockupQrCode;
                        pblog.CreateTime = DateTime.Now;
                        pblog.State = 1;
                        await Db.InsertAsync<PB_StockLog>(pblog);

                        //删除库存
                        await ExecuteSqlAsync("delete from PB_Stock where PackId='" + packid + "'");

                        //删除库存明细表
                        await ExecuteSqlAsync("delete from IT_LocalDetail where PackId='" + packid + "'");

                        //修改包的状态为已出库
                        await ExecuteSqlAsync("update PB_PackList set Status=4,OutStorTime=NOW() where Id='" + packid + "'");
                    }
                    else
                    {
                        throw new BusException("请扫描正确的货位上的包信息");
                    }
                }
            }
            else if (packmodel == "04")
            {
                List<IT_LocalDetail> IT_LocalDetaillist = await Db.GetIQueryable<IT_LocalDetail>().Where(x => x.MaterialId == materialId && x.PackId == "" && x.LocalId == locaId).ToListAsync();
                if (IT_LocalDetaillist.Count > 0)
                {
                    //汇总当前货位的物料总数量
                    var matersumnums = IT_LocalDetaillist.GroupBy(x => new { x.MaterialId, x.StorId, x.LocalId }).Select(x => new { MaterialId = x.Key.MaterialId, LocalId = x.Key.LocalId, StorId = x.Key.StorId, materNums = x.Sum(i => i.Num) }).FirstOrDefault();
                    if (data.Nums > matersumnums.materNums)
                    {
                        throw new BusException("请检查当前货位物料的数量是否满足出库的数量");
                    }

                    //获取要出库的单个物料数量
                    double? warehouseNum = data.Nums;
                    string itsql = string.Empty;
                    string strLocalId = string.Empty;
                    string strStorId = string.Empty;
                    foreach (var item in IT_LocalDetaillist)
                    {
                        if (warehouseNum > 0)
                        {
                            strLocalId = item.LocalId;
                            strStorId = item.StorId;
                            //出库数量等于库存明细数量时
                            if (item.Num == warehouseNum)
                            {
                                warehouseNum -= item.Num;
                                if (item.OldOrNew == 0)
                                {
                                    itsql += @" update IT_LocalMaterial set Nums_O=IFNULL(Nums_O,0)-" + item.Num + ",ScanNum=IFNULL(ScanNum,0)-" + item.Num + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                                }
                                else if (item.OldOrNew == 1)
                                {
                                    itsql += @" update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)-" + item.Num + ",ScanNum=IFNULL(ScanNum,0)-" + item.Num + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                                }
                                itsql += @" delete from IT_LocalDetail where Id='" + item.Id + "'; ";

                            }
                            else if (item.Num < warehouseNum)   //出库数量大于库存明细数量时
                            {
                                if (warehouseNum >= item.Num)
                                {
                                    warehouseNum -= item.Num;
                                    if (item.OldOrNew == 0)
                                    {
                                        itsql += @" update IT_LocalMaterial set Nums_O=IFNULL(Nums_O,0)-" + item.Num + ",ScanNum=IFNULL(ScanNum,0)-" + item.Num + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                                    }
                                    else if (item.OldOrNew == 1)
                                    {
                                        itsql += @" update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)-" + item.Num + ",ScanNum=IFNULL(ScanNum,0)-" + item.Num + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                                    }
                                    itsql += @" delete from IT_LocalDetail where Id='" + item.Id + "'; ";
                                }
                                else
                                {
                                    warehouseNum -= warehouseNum;
                                    if (item.OldOrNew == 0)
                                    {
                                        itsql += @" update IT_LocalMaterial set Nums_O=IFNULL(Nums_O,0)-" + warehouseNum + ",ScanNum=IFNULL(ScanNum,0)-" + warehouseNum + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                                    }
                                    else if (item.OldOrNew == 1)
                                    {
                                        itsql += @" update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)-" + warehouseNum + ",ScanNum=IFNULL(ScanNum,0)-" + warehouseNum + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                                    }
                                    itsql += @" update IT_LocalDetail set Num=IFNULL(Num,0)-" + warehouseNum + " where Id='" + item.Id + "'; ";
                                }
                            }
                            else if (item.Num > warehouseNum) //出库数量小于库存明细数量时
                            {
                                item.Num -= warehouseNum;
                                if (item.OldOrNew == 0)
                                {
                                    itsql += @" update IT_LocalMaterial set Nums_O=IFNULL(Nums_O,0)-" + warehouseNum + ",ScanNum=IFNULL(ScanNum,0)-" + warehouseNum + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                                }
                                else if (item.OldOrNew == 1)
                                {
                                    itsql += @" update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)-" + warehouseNum + ",ScanNum=IFNULL(ScanNum,0)-" + warehouseNum + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                                }
                                itsql += @" update IT_LocalDetail set Num=IFNULL(Num,0)-" + warehouseNum + " where Id='" + item.Id + "'; ";
                                warehouseNum -= warehouseNum;
                            }

                        }
                        else
                        {
                            continue;
                        }
                    }
                    itsql += @" update PB_DispatchDetail set ReadyNums=IFNULL(ReadyNums,0)+" + data.Nums + " where Id='" + stockupQrCode + "'; ";
                    await ExecuteSqlAsync(itsql);

                    //获取要出库的单个物料数量
                    warehouseNum = data.Nums;
                    itsql = "";
                    strLocalId = "";
                    strStorId = "";

                    List<PB_Stock> PB_Stocklist = await Db.GetIQueryable<PB_Stock>().Where(x => x.MaterialId == materialId && x.PackId == "" && x.LocationId == locaId).ToListAsync();
                    if (PB_Stocklist.Count > 0)
                    {
                        foreach (var item in PB_Stocklist)
                        {
                            if (warehouseNum != 0)
                            {
                                strLocalId = item.LocationId;
                                strStorId = item.StorId;
                                //出库数量等于库存明细数量时
                                if (item.StockNumber == warehouseNum)
                                {
                                    warehouseNum -= item.StockNumber;
                                    itsql += @" delete from PB_Stock where Id='" + item.Id + "'; ";
                                }
                                else if (item.StockNumber < warehouseNum)   //出库数量大于库存明细数量时
                                {
                                    if (warehouseNum >= item.StockNumber)
                                    {
                                        warehouseNum -= item.StockNumber;
                                        itsql += @" delete from PB_Stock where Id='" + item.Id + "'; ";
                                    }
                                    else
                                    {
                                        warehouseNum -= warehouseNum;
                                        itsql += @" update PB_Stock set StockNumber=IFNULL(StockNumber,0)-" + warehouseNum + " where Id='" + item.Id + "'; ";
                                    }
                                }
                                else if (item.StockNumber > warehouseNum) //出库数量小于库存明细数量时
                                {
                                    item.StockNumber -= warehouseNum;
                                    itsql += @" update PB_Stock set StockNumber=IFNULL(StockNumber,0)-" + warehouseNum + " where Id='" + item.Id + "'; ";
                                }
                            }
                            else
                            {
                                continue;
                            }
                        }
                        await ExecuteSqlAsync(itsql);
                    }
                    #region 生产领料需要走备货日志
                    PB_DispatchDetail pickupdata = await Db.GetIQueryable<PB_DispatchDetail>().Where(x => x.Id == stockupQrCode).FirstOrDefaultAsync();
                    //增加备货单明细日志
                    PB_DispatchLoadList pbdata = new PB_DispatchLoadList();
                    pbdata.Id = IdHelper.GetId();
                    pbdata.ParentId = pickupdata.Id;
                    pbdata.LocalId = strLocalId;
                    pbdata.PackId = "";
                    pbdata.MaterialId = materialId;
                    pbdata.Nums = data.Nums;
                    pbdata.CreatorId = _operator.UserId;
                    pbdata.CreatorRealName = bdata.RealName;
                    pbdata.CreateTime = DateTime.Now;
                    pbdata.Deleted = false;
                    pbdata.State = 1;
                    await Db.InsertAsync<PB_DispatchLoadList>(pbdata);
                    #endregion
                    //增加出库日志
                    PB_StockLog pblog = new PB_StockLog();
                    pblog.Id = IdHelper.GetId();
                    pblog.PackId = packid;
                    pblog.StorId = strStorId;
                    pblog.LocationId = strLocalId;
                    pblog.MaterialId = materialId;
                    pblog.StockNumber = data.Nums;
                    pblog.CreatorId = _operator.UserId;
                    pblog.CreatorName = bdata.RealName;
                    pblog.OperatorId = _operator.UserId;
                    pblog.Type = 1;
                    pblog.OrderID = stockupQrCode;
                    pblog.CreateTime = DateTime.Now;
                    pblog.State = 1;
                    await Db.InsertAsync<PB_StockLog>(pblog);
                }
                else
                {
                    throw new BusException("请检查货位上的库存物料是否满足需求");
                }

            }
        }

        /// <summary>
        /// 删除生产领料单已出库的明细
        /// </summary>
        /// <param name="stockupQrCode">生产任务</param>
        /// <param name="packqrocde">包二维码</param>
        /// <param name="Nums">数量</param>
        /// <returns></returns>
        [Transactional]
        public async Task DeleteStockUpDetailListDataAsync(string stockupQrCode, string packqrocde, double Nums)
        {
            Base_User basedata = await Db.GetIQueryable<Base_User>().Where(x => x.Id == _operator.UserId).FirstOrDefaultAsync();
            string stockupid = stockupQrCode.Split(',')?[1].ToString();   //生产任务单id
            string packid = string.Empty;
            string materialId = string.Empty;
            string packmodel = packqrocde.Split(',')?[0].ToString();
            int State = 1;
            if (packmodel == "13")
            {
                packid = packqrocde.Split(',')?[1].ToString();
            }
            else if (packmodel == "04")
            {
                var model = packqrocde.Split(',');
                if (model.Length > 2)
                {
                    materialId = model?[3].ToString();
                }
                else
                {
                    materialId = model?[1].ToString();
                }
            }
            PB_DispatchDetail dispatchDetail = await Db.GetIQueryable<PB_DispatchDetail>().Where(x => x.Id == stockupid).FirstOrDefaultAsync();
            if (packmodel == "13")
            {
                string sqlMain = string.Empty;
                
                PB_PackList pbdata = await Db.GetIQueryable<PB_PackList>().Where(x => x.Id == packid).FirstOrDefaultAsync();
                if (pbdata.PackageType == 0)
                {
                    Expression<Func<PB_DispatchLoadList, PB_Location, PB_Material, PB_DispatchLoadListDataList>> packselect = (a, b, c) => new PB_DispatchLoadListDataList
                    {
                        LocationId = b.Id,
                        StorId = b.StorId,
                        AreaId = b.AreaId,
                        RackId = b.RackId,
                        MeasureId = c.storageId
                    };
                    packselect = packselect.BuildExtendSelectExpre();
                    var packdata = from a in Db.GetIQueryable<PB_DispatchLoadList>().AsExpandable()
                                   join b in Db.GetIQueryable<PB_Location>() on a.LocalId equals b.Id into ab
                                   from b in ab.DefaultIfEmpty()
                                   join c in Db.GetIQueryable<PB_Material>() on a.MaterialId equals c.Id into ac
                                   from c in ac.DefaultIfEmpty()
                                   select @packselect.Invoke(a, b, c);
                    var unloaddata = await packdata.Where(x => x.PackId == packid && x.ParentId == stockupid).FirstOrDefaultAsync();
                    //删除备货出库的明细
                    sqlMain += @" delete from PB_DispatchLoadList where PackId='" + packid + "' and ParentId='" + stockupid + "' ;";
                    //修改包的状态为入库
                    sqlMain += @" update PB_PackList set Status=3,InStorTime='"+DateTime.Now+"' where Id='" + packid + "'; ";
                    //减去备货单明细的出库数量
                    sqlMain += @" update PB_DispatchDetail set ReadyNums=IFNULL(ReadyNums,0)-" + pbdata.Nums + " where Id='" + stockupid + "'; ";
                    await ExecuteSqlAsync(sqlMain);

                    PB_Stock stockdata = new PB_Stock();
                    stockdata.Id = IdHelper.GetId();
                    stockdata.PackId = packid;
                    stockdata.StorId = unloaddata.StorId;
                    stockdata.LocationId = unloaddata.LocationId;
                    stockdata.StockNumber = pbdata.Nums;
                    stockdata.MaterialId = pbdata.MaterialId;
                    stockdata.CreatorId = _operator.UserId;
                    stockdata.CreatorName = basedata.RealName;
                    stockdata.CreateTime = DateTime.Now;
                    stockdata.OperatorId = _operator.UserId;
                    stockdata.Type = 2;
                    stockdata.OrderID = stockupid;
                    stockdata.OldOrNew = pbdata.OldOrNew;
                    stockdata.AreaId = unloaddata.AreaId;
                    stockdata.RackId = unloaddata.RackId;
                    await Db.InsertAsync<PB_Stock>(stockdata);

                    PB_StockLog stockdatalog = new PB_StockLog();
                    stockdatalog.Id = IdHelper.GetId();
                    stockdatalog.PackId = packid;
                    stockdatalog.StorId = unloaddata.StorId;
                    stockdatalog.LocationId = unloaddata.LocationId;
                    stockdatalog.StockNumber = pbdata.Nums;
                    stockdatalog.MaterialId = pbdata.MaterialId;
                    stockdatalog.CreatorId = _operator.UserId;
                    stockdatalog.CreatorName = basedata.RealName;
                    stockdatalog.CreateTime = DateTime.Now;
                    stockdatalog.OperatorId = _operator.UserId;
                    stockdatalog.Type = 2;
                    stockdatalog.OrderID = stockupid;
                    stockdatalog.OldOrNew = pbdata.OldOrNew;
                    stockdatalog.State = 0;
                    await Db.InsertAsync<PB_StockLog>(stockdatalog);

                    IT_LocalDetail itdata = new IT_LocalDetail();
                    itdata.Id = IdHelper.GetId();
                    itdata.StorId = unloaddata.StorId;
                    itdata.LocalId = unloaddata.LocationId;
                    itdata.MaterialId = pbdata.MaterialId;
                    itdata.MeasureId = unloaddata.MeasureId;
                    itdata.BarCode = pbdata.QrCode;
                    itdata.InTime = DateTime.Now;
                    itdata.CreateTime = DateTime.Now;
                    itdata.CreatorId = _operator.UserId;
                    itdata.Deleted = false;
                    itdata.Num = pbdata.Nums;
                    itdata.PackId = packid;
                    itdata.OldOrNew = pbdata.OldOrNew;
                    itdata.JobId = "";
                    await Db.InsertAsync<IT_LocalDetail>(itdata);

                    sqlMain = "";
                    if (pbdata.OldOrNew == 0)
                    {
                        sqlMain = @" update IT_LocalMaterial set Nums_O=IFNULL(Nums_O,0)+" + pbdata.Nums + ",ScanNum=IFNULL(ScanNum,0)+" + pbdata.Nums + " where MaterialId='" + pbdata.MaterialId + "' and StorId='" + unloaddata.StorId + "' and LocalId='" + unloaddata.LocationId + "' ";
                    }
                    else if (pbdata.OldOrNew == 1)
                    {
                        sqlMain = @" update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)+" + pbdata.Nums + ",ScanNum=IFNULL(ScanNum,0)+" + pbdata.Nums + " where MaterialId='" + pbdata.MaterialId + "' and StorId='" + unloaddata.StorId + "' and LocalId='" + unloaddata.LocationId + "' ";
                    }
                    await ExecuteSqlAsync(sqlMain);
                }
                else if (pbdata.PackageType == 1)
                {
                    //PB_DispatchLoadListDataList
                    Expression<Func<PB_DispatchLoadList, PB_Location, PB_Material, PB_DispatchLoadListDataList>> packselect = (a, b, c) => new PB_DispatchLoadListDataList
                    {
                        LocationId = b.Id,
                        StorId = b.StorId,
                        AreaId = b.AreaId,
                        RackId = b.RackId,
                        MeasureId = c.storageId
                    };
                    packselect = packselect.BuildExtendSelectExpre();
                    var packdata = from a in Db.GetIQueryable<PB_DispatchLoadList>().AsExpandable()
                                   join b in Db.GetIQueryable<PB_Location>() on a.LocalId equals b.Id into ab
                                   from b in ab.DefaultIfEmpty()
                                   join c in Db.GetIQueryable<PB_Material>() on a.MaterialId equals c.Id into ac
                                   from c in ac.DefaultIfEmpty()
                                   select @packselect.Invoke(a, b, c);
                    var unloaddata = await packdata.Where(x => x.PackId == packid && x.ParentId == stockupid).ToListAsync();

                    //删除备货出库的明细
                    sqlMain += @" delete from PB_DispatchLoadList where PackId='" + packid + "'and ParentId='" + stockupid + "' ; ";
                    //修改包的状态为入库
                    sqlMain += @" update PB_PackList set Status=3,InStorTime='" + DateTime.Now + "'  where Id='" + packid + "'; ";
                    await ExecuteSqlAsync(sqlMain);

                    string strStorId = string.Empty;
                    string strLocalId = string.Empty;
                    string strAreaId = string.Empty;
                    string strRackId = string.Empty;
                    List<IT_LocalDetail> LocalDetaillist = new List<IT_LocalDetail>();
                    sqlMain = "";
                    foreach (var item in unloaddata)
                    {
                        strStorId = item.StorId;
                        strLocalId = item.LocalId;
                        strAreaId = item.AreaId;
                        strRackId = item.RackId;
                        IT_LocalDetail itdata = new IT_LocalDetail();
                        itdata.Id = IdHelper.GetId();
                        itdata.StorId = item.StorId;
                        itdata.LocalId = item.LocalId;
                        itdata.MaterialId = item.MaterialId;
                        itdata.MeasureId = item.MeasureId;
                        itdata.BarCode = pbdata.QrCode;
                        itdata.InTime = DateTime.Now;
                        itdata.CreateTime = DateTime.Now;
                        itdata.CreatorId = _operator.UserId;
                        itdata.Deleted = false;
                        itdata.Num = item.Nums;
                        itdata.PackId = packid;
                        itdata.OldOrNew = pbdata.OldOrNew;
                        itdata.JobId = "";
                        LocalDetaillist.Add(itdata);

                        //减去备货单明细的出库数量
                        sqlMain += @" update PB_DispatchDetail set ReadyNums=IFNULL(ReadyNums,0)-" + item.Nums + " where Id='" + stockupid + "'; ";

                        if (pbdata.OldOrNew == 0)
                        {
                            sqlMain += @" update IT_LocalMaterial set Nums_O=IFNULL(Nums_O,0)+" + item.Nums + ",ScanNum=IFNULL(ScanNum,0)+" + item.Nums + " where MaterialId='" + item.MaterialId + "' and StorId='" + item.StorId + "' and LocalId='" + item.LocationId + "'; ";
                        }
                        else if (pbdata.OldOrNew == 1)
                        {
                            sqlMain += @" update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)+" + item.Nums + ",ScanNum=IFNULL(ScanNum,0)+" + item.Nums + " where MaterialId='" + item.MaterialId + "' and StorId='" + item.StorId + "' and LocalId='" + item.LocationId + "'; ";
                        }
                    }
                    //添加库存汇总
                    await ExecuteSqlAsync(sqlMain);
                    //添加库存明细
                    await Db.InsertAsync<IT_LocalDetail>(LocalDetaillist);

                    //添加PB_Stock
                    PB_Stock stockdata = new PB_Stock();
                    stockdata.Id = IdHelper.GetId();
                    stockdata.PackId = packid;
                    stockdata.StorId = strStorId;
                    stockdata.LocationId = strLocalId;
                    stockdata.StockNumber = pbdata.Nums;
                    stockdata.MaterialId = pbdata.MaterialId;
                    stockdata.CreatorId = _operator.UserId;
                    stockdata.CreatorName = basedata.RealName;
                    stockdata.CreateTime = DateTime.Now;
                    stockdata.OperatorId = _operator.UserId;
                    stockdata.Type = 2;
                    stockdata.OrderID = stockupid;
                    stockdata.OldOrNew = pbdata.OldOrNew;
                    stockdata.AreaId = strAreaId;
                    stockdata.RackId = strRackId;
                    await Db.InsertAsync<PB_Stock>(stockdata);

                    //添加PB_StockLog
                    PB_StockLog stockdatalog = new PB_StockLog();
                    stockdatalog.Id = IdHelper.GetId();
                    stockdatalog.PackId = packid;
                    stockdatalog.StorId = strStorId;
                    stockdatalog.LocationId = strLocalId;
                    stockdatalog.StockNumber = pbdata.Nums;
                    stockdatalog.MaterialId = pbdata.MaterialId;
                    stockdatalog.CreatorId = _operator.UserId;
                    stockdatalog.CreatorName = basedata.RealName;
                    stockdatalog.CreateTime = DateTime.Now;
                    stockdatalog.OperatorId = _operator.UserId;
                    stockdatalog.Type = 2;
                    stockdatalog.OrderID = stockupid;
                    stockdatalog.OldOrNew = pbdata.OldOrNew;
                    stockdatalog.State = 0;
                    await Db.InsertAsync<PB_StockLog>(stockdatalog);
                }
            }
            else
            {
                string sqlMain = string.Empty;
                Expression<Func<PB_DispatchLoadList, PB_Location, PB_DispatchDetail, PB_Material, PB_DispatchLoadListDataList>> packselect = (a, b, c, d) => new PB_DispatchLoadListDataList
                {
                    LocationId = b.Id,
                    StorId = b.StorId,
                    AreaId = b.AreaId,
                    RackId = b.RackId,
                    PickListMainId = c.ParentId,
                    PickListId = c.Id,
                    MeasureId = d.storageId,
                };
                packselect = packselect.BuildExtendSelectExpre();
                var packdata = from a in Db.GetIQueryable<PB_DispatchLoadList>().AsExpandable()
                               join b in Db.GetIQueryable<PB_Location>() on a.LocalId equals b.Id into ab
                               from b in ab.DefaultIfEmpty()
                               join c in Db.GetIQueryable<PB_DispatchDetail>() on a.ParentId equals c.Id into ac
                               from c in ac.DefaultIfEmpty()
                               join d in Db.GetIQueryable<PB_Material>() on a.MaterialId equals d.Id into ad
                               from d in ad.DefaultIfEmpty()
                               select @packselect.Invoke(a, b, c, d);
                var unloaddata = await packdata.Where(x => x.PickListId == stockupid && x.MaterialId == materialId && x.State == State).FirstOrDefaultAsync();

                //删除派工单备货出库的明细
                sqlMain += @" delete from PB_DispatchLoadList where MaterialId='" + unloaddata.MaterialId + "' and ParentId='" + unloaddata.ParentId + "' and LocalId='" + unloaddata.LocationId + "' and Nums='" + Nums + "' and State= 1  LIMIT 1; ";
                //减去派工单明细的出库数量
                sqlMain += @" update PB_DispatchDetail set ReadyNums=IFNULL(ReadyNums,0)-" + Nums + " where Id='" + stockupid + "'; ";
                await ExecuteSqlAsync(sqlMain);

                PB_Stock stockdata = new PB_Stock();
                stockdata.Id = IdHelper.GetId();
                stockdata.PackId = packid;
                stockdata.StorId = unloaddata.StorId;
                stockdata.LocationId = unloaddata.LocationId;
                stockdata.StockNumber = Nums;
                stockdata.MaterialId = unloaddata.MaterialId;
                stockdata.CreatorId = _operator.UserId;
                stockdata.CreatorName = basedata.RealName;
                stockdata.CreateTime = DateTime.Now;
                stockdata.OperatorId = _operator.UserId;
                stockdata.Type = 2;
                stockdata.OrderID = stockupid;
                stockdata.OldOrNew = 1;
                stockdata.AreaId = unloaddata.AreaId;
                stockdata.RackId = unloaddata.RackId;
                await Db.InsertAsync<PB_Stock>(stockdata);

                PB_StockLog stockdatalog = new PB_StockLog();
                stockdatalog.Id = IdHelper.GetId();
                stockdatalog.PackId = packid;
                stockdatalog.StorId = unloaddata.StorId;
                stockdatalog.LocationId = unloaddata.LocationId;
                stockdatalog.StockNumber = Nums;
                stockdatalog.MaterialId = unloaddata.MaterialId;
                stockdatalog.CreatorId = _operator.UserId;
                stockdatalog.CreatorName = basedata.RealName;
                stockdatalog.CreateTime = DateTime.Now;
                stockdatalog.OperatorId = _operator.UserId;
                stockdatalog.Type = 2 ;
                stockdatalog.OrderID = stockupid;
                stockdatalog.OldOrNew = 1;
                stockdatalog.State = 0;
                await Db.InsertAsync<PB_StockLog>(stockdatalog);

                IT_LocalDetail itdata = new IT_LocalDetail();
                itdata.Id = IdHelper.GetId();
                itdata.StorId = unloaddata.StorId;
                itdata.LocalId = unloaddata.LocationId;
                itdata.MaterialId = unloaddata.MaterialId;
                itdata.MeasureId = unloaddata.MeasureId;
                itdata.BarCode = packqrocde;
                itdata.InTime = DateTime.Now;
                itdata.CreateTime = DateTime.Now;
                itdata.CreatorId = _operator.UserId;
                itdata.Deleted = false;
                itdata.Num = Nums;
                itdata.PackId = packid;
                itdata.OldOrNew = 1;
                itdata.JobId = "";
                await Db.InsertAsync<IT_LocalDetail>(itdata);

                sqlMain = "";
                sqlMain = @" update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)+" + Nums + ",ScanNum=IFNULL(ScanNum,0)+" + Nums + " where MaterialId='" + unloaddata.MaterialId + "' and StorId='" + unloaddata.StorId + "' and LocalId='" + unloaddata.LocationId + "' ";
                await ExecuteSqlAsync(sqlMain);

            }
        }

        /// <summary>
        /// 下架出库提交
        /// </summary>
        /// <param name="stockupQrCode">生产领料单二维码</param>
        /// 增加项目备货记录  
        /// 修改出库明细汇总物料ID   
        /// <returns></returns>
        [Transactional]
        public async Task SubmitStockUpDataAsync(string stockupQrCode)
        {
            //生产领料单二维码
            string stockupid = stockupQrCode.Split(',')?[1].ToString();
            int State = 1;
            Base_User userdata = await Db.GetIQueryable<Base_User>().Where(x => x.Id == _operator.UserId).FirstOrDefaultAsync();
            Expression<Func<PB_DispatchLoadList, PB_Material, PB_DispatchDetail, PB_DispatchLoadListDataList>> packselect = (a, b,c) => new PB_DispatchLoadListDataList
            {
                Price = b.Price,
                LocalId = a.LocalId
            };
            packselect = packselect.BuildExtendSelectExpre();
            var packdata = from a in Db.GetIQueryable<PB_DispatchLoadList>().AsExpandable()
                           join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
                           from b in ab.DefaultIfEmpty()
                           join c in Db.GetIQueryable<PB_DispatchDetail>() on a.ParentId equals c.Id into ac
                           from c in ac.DefaultIfEmpty()
                           select @packselect.Invoke(a, b,c);
            var stockupData = packdata.Where(x => x.ParentId == stockupid && x.State == State).ToList();
            List<string> listdata = new List<string>();
            string sqlMain = "";
            int indexstate = 0;
            //foreach (var item in stockupData)
            //{
            //    if (item.ReadyNums == item.Nums)
            //    {
            //        sqlMain += $@" update PB_DispatchDetail set State=1 where Id='" + item.Id + "'; ";
            //        if (indexstate != 2 || indexstate == 0)
            //        {
            //            indexstate = 1;
            //        }
            //    }
            //    else
            //    {
            //        sqlMain += $@" update PB_DispatchDetail set State=2 where Id='" + item.Id + "'; ";
            //        indexstate = 2;
            //    }
            //}
            sqlMain += $@" update PB_DispatchDetail set State=2 where Id='" + stockupid + "'; ";
            //修改备货单明细状态
            await ExecuteSqlAsync(sqlMain);

            //定义备货单状态
            int status = 0;
            //if (indexstate == 1)
            //{
            status = 3;
            double dbnum = 0;
            string outid = IdHelper.GetId();
            List<TD_OutStorDetail> listdetaildata = new List<TD_OutStorDetail>();
            int i = 0;
            string StorId = string.Empty;
            string AreaId = string.Empty;
            string RackId = string.Empty;
            foreach (var item in stockupData)
            {
                if (i == 0)
                {
                    var locadata = await Db.GetIQueryable<PB_Location>().Where(x => x.Id == item.LocalId).FirstOrDefaultAsync();
                    StorId = locadata.StorId;
                    AreaId = locadata.AreaId;
                    RackId = locadata.RackId;
                }
                TD_OutStorDetail outdetaildata = new TD_OutStorDetail();
                outdetaildata.Id = IdHelper.GetId();
                outdetaildata.StorId = StorId;
                outdetaildata.OutStorId = outid;
                outdetaildata.LocalId = item.LocalId;
                outdetaildata.AreaId = AreaId;
                outdetaildata.RackId = RackId;
                outdetaildata.MaterialId = item.MaterialId;
                outdetaildata.OutNum = Convert.ToDouble(item.Nums);
                outdetaildata.Price = Convert.ToDouble(item.Price);
                outdetaildata.TotalAmt = Convert.ToDouble(item.Nums) * Convert.ToDouble(item.Price);
                dbnum += Convert.ToDouble(item.Nums);
                outdetaildata.CreatorId = _operator.UserId;
                outdetaildata.CreateTime = DateTime.Now;
                outdetaildata.Deleted = false;
                listdetaildata.Add(outdetaildata);
                i++;
            }
            await Db.InsertAsync<TD_OutStorDetail>(listdetaildata);
            //添加出库明细表
            List<TD_OutStorRecord> listtrecorddata = new List<TD_OutStorRecord>();
            var listd = listdetaildata.GroupBy(x => new { x.StorId, x.LocalId, x.OutStorId, x.MaterialId, x.Price }).Select(x => new { StorId = x.Key.StorId, LocalId = x.Key.LocalId, OutStorId = x.Key.OutStorId, MaterialId = x.Key.MaterialId, Price = x.Key.Price, Nums = x.Sum(n => n.OutNum) });
            foreach (var item in listd)
            {
                TD_OutStorRecord recodata = new TD_OutStorRecord();
                recodata.Id = IdHelper.GetId();
                recodata.StorId = item.StorId;
                recodata.OutStorId = item.OutStorId;
                recodata.MaterialId = item.MaterialId;
                recodata.OutNum = item.Nums;
                recodata.Price = item.Price;
                recodata.TotalAmt = item.Nums * item.Price;
                recodata.CreateTime = DateTime.Now;
                recodata.CreatorId = _operator.UserId;
                recodata.Deleted = false;
                listtrecorddata.Add(recodata);
            }
            await Db.InsertAsync<TD_OutStorRecord>(listtrecorddata);

            var deptdata = await _opdept.GetOperatorDept();

            //根据备货单获取项目Id和设计清单Id
            List<PB_DispatchDetailPDADTO> list = new List<PB_DispatchDetailPDADTO>();
            Expression<Func<PB_DispatchDetail, PB_Material, PB_Project, PB_ProductionOrder, PB_ProcecureSub, PB_Dispatch, PB_DispatchDetailPDADTO>> select = (a, b, c, d, e, f) => new PB_DispatchDetailPDADTO
            {
                MaterialIdName = b.Name,
                MateralldCode = b.QrCode,
                ProName = c.Name,
                ProductionOrderName = d.POName,
                BatchNumber = d.BatchNumber,
                Code = d.Code,
                SubName = e.Name,
                StaffId = f.StaffId,
                StaffName = f.StaffName
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<PB_DispatchDetail>().AsExpandable()
                    join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<PB_Project>() on a.ProjectId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<PB_ProductionOrder>() on a.OrderId equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    join e in Db.GetIQueryable<PB_ProcecureSub>() on a.SubId equals e.Id into ae
                    from e in ae.DefaultIfEmpty()
                    join f in Db.GetIQueryable<PB_Dispatch>() on a.ParentId equals f.Id into af
                    from f in af.DefaultIfEmpty()
                    select @select.Invoke(a, b, c, d, e, f);

            var PickUpMainProjectdata = await q.Where(x => x.Id == stockupid).FirstOrDefaultAsync();


            //创建出库单
            TD_OutStorage outdata = new TD_OutStorage();
            outdata.Id = outid;
            outdata.Code = DateTime.Now.ToString("yyyyMMddHHmmss");
            outdata.OutTime = DateTime.Now;
            outdata.StorageId = StorId;
            outdata.OutType = "ProductOut";
            outdata.RefCode = stockupid;
            outdata.ProjectId = PickUpMainProjectdata.ProjectId;
            //outdata.DesignId = PickUpMainProjectdata.DesignId;
            outdata.OutNum = dbnum;
            outdata.CreatorId = _operator.UserId;
            outdata.Status = 0;
            outdata.CreateTime = DateTime.Now;
            outdata.Deleted = false;
            outdata.QrCode = h_OutStorage + "," + outid;
            outdata.DepId = deptdata.DetpId;
            outdata.FactoryId = userdata.FactoryId;
            await Db.InsertAsync<TD_OutStorage>(outdata);


            Expression<Func<PB_DispatchLoadList, PB_Material, PB_DispatchDetail, PB_DispatchLoadListDataList>> selectq = (a, b, c) => new PB_DispatchLoadListDataList
            {
                Price = b.Price,
                LocalId = a.LocalId
            };
            selectq = selectq.BuildExtendSelectExpre();
            var qd = from a in Db.GetIQueryable<PB_DispatchLoadList>().AsExpandable()
                           join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
                           from b in ab.DefaultIfEmpty()
                           join c in Db.GetIQueryable<PB_DispatchDetail>() on a.ParentId equals c.Id into ac
                           from c in ac.DefaultIfEmpty()
                           select selectq.Invoke(a, b, c);
            var qdata = qd.Where(x => x.ParentId == stockupid).ToList();

            var listdetdata = qdata.GroupBy(x => new { x.MaterialId, x.Nums, x.LocalId }).Select(x => new { MaterialId = x.Key.MaterialId, StockVolume = x.Key.Nums, LocationId = x.Key.LocalId }).ToList();
            //string sqlMains = string.Empty;
            ////foreach (var item in listdetdata)
            ////{
            ////    sqlMains += @" update IT_LocalMaterial set Nums_R=IFNULL(Nums_R,0)-" + item.StockVolume + " where MaterialId='" + item.MaterialId + "' and LocalId='" + item.LocationId + "'; ";
            ////}
            ////清楚库存备用单占用量
            //await ExecuteSqlAsync(sqlMains);

            //修改生产任务单状态
            string sqlstr = @"update PB_DispatchDetail set State=" + status + " where id='" + stockupid + "';";
             sqlstr += @"update PB_DispatchLoadList set State= 2 where ParentId='" + stockupid + "';";
            await ExecuteSqlAsync(sqlstr);
        }


        /// <summary>
        /// 取消备货单提交
        /// </summary>
        /// <param name="stockupQrCode">备货单二维码</param>
        /// 增加取消备货单提交功能   PB_DispatchLoadListPriceDTO
        /// <returns></returns>
        [Transactional]
        public async Task CancelStockUpAsync(string stockupQrCode)
        {
            //备货单Id
            string pickmainId = stockupQrCode.Split(',')?[1].ToString();
            Expression<Func< PB_DispatchLoadList, PB_DispatchDetail, PB_DispatchLoadListDataList >> select = (a, b) => new PB_DispatchLoadListDataList
            {
               
            };
            select = select.BuildExtendSelectExpre(); 
             var qd = from a in Db.GetIQueryable<PB_DispatchLoadList>().AsExpandable()
                     join b in Db.GetIQueryable<PB_DispatchDetail>() on a.Id equals b.ParentId into ab
                     from b in ab.DefaultIfEmpty()
                     select @select.Invoke(a, b);
            var qdata = await qd.Where(x => x.ParentId == pickmainId).ToListAsync();
            var listdetdata = qdata.GroupBy(x => new { x.MaterialId, x.Nums, x.LocalId }).Select(x => new { MaterialId = x.Key.MaterialId, StockVolume = x.Key.Nums, LocationId = x.Key.LocalId }).ToList();
            string sqlMains = string.Empty;
            foreach (var item in listdetdata)
            {
                sqlMains += @" update IT_LocalMaterial set Nums_R=IFNULL(Nums_R,0)+" + item.StockVolume + " where MaterialId='" + item.MaterialId + "' and LocalId='" + item.LocationId + "'; ";
            }
            //取消备货单提交后恢复库存备用单占用量
            await ExecuteSqlAsync(sqlMains);

            //获取出库单信息
            TD_OutStorage outdata = await Db.GetIQueryable<TD_OutStorage>().Where(x => x.RefCode == pickmainId).FirstOrDefaultAsync();
            //删除出库单信息
            sqlMains = "";
            sqlMains = @" delete from TD_OutStorRecord where OutStorId='" + outdata.Id + "'; delete from TD_OutStorDetail where OutStorId='" + outdata.Id + "'; delete from TD_OutStorage where Id='" + outdata.Id + "';    ";
            await ExecuteSqlAsync(sqlMains);
            //修改备货单状态为开始作业
            string sqlstr = @"update PB_DispatchDetail set State = 2 where Id='" + pickmainId + "'";
            await ExecuteSqlAsync(sqlstr);
        }








        /// <summary>
        /// 扫描流转单二维码
        /// </summary>
        /// <param name="carculqrcode"></param>
        /// <returns></returns>
        public async Task<PB_CirculationBillPDADTO> GetPDACarCulDataListInfo(string carculqrcode)
        {
            if (!carculqrcode.IsNullOrEmpty())
            {
                if (carculqrcode.Split(',')?[0].ToString() == "31")
                {
                    string carculid = carculqrcode.Split(',')?[1].ToString();
                    Expression<Func<PB_CirculationBill, PB_Dispatch, PB_DispatchDetail, PB_Material, PB_Tray, PB_CirculationBillPDADTO>> select = (a, b, c, d, e) => new PB_CirculationBillPDADTO
                    {
                        StaffName = b.StaffName,
                        MaterialName = d.Name,
                        MaterialCode = d.Code,
                        DispatchDetailId = c.Id,
                        TryName = e.Code +"/"+e.Name

                    };
                    select = select.BuildExtendSelectExpre();
                    var q = from a in Db.GetIQueryable<PB_CirculationBill>().AsExpandable()
                            join b in Db.GetIQueryable<PB_Dispatch>() on  a.DispatchId  equals   b.Id  into ab
                            from b in ab.DefaultIfEmpty()
                            join c in Db.GetIQueryable<PB_DispatchDetail>() on new { ParentId = b.Id, a.MaterialId } equals new { c.ParentId, c.MaterialId } into abc
                            from c in abc.DefaultIfEmpty()
                            join d in Db.GetIQueryable<PB_Material>() on c.MaterialId equals d.Id into cd
                            from d in cd.DefaultIfEmpty()
                            join e in Db.GetIQueryable<PB_Tray>() on a.TrayId equals e.Id into ae
                            from e in ae.DefaultIfEmpty()
                            select @select.Invoke(a, b, c, d, e);
                    var qdata = await q.Where(x => x.Id == carculid).FirstOrDefaultAsync();
                    if (qdata == null)
                    {
                        throw new BusException("请扫描或输入正确的流转单二维码!");
                    }
                    else if (qdata.WorkerId.IsNullOrEmpty() || qdata.Statistician.IsNullOrEmpty())
                    {
                        throw new BusException("请扫描或输入正确的流转单二维码!");
                    }
                    else if (qdata.ReadyNums >= qdata.StatisticalQuantity)
                    {
                        throw new BusException("该托盘没有物料可取了,请重新扫描流转单!");
                    }
                    else
                    {
                        return qdata;
                    }
                    
                }
                else
                {
                    throw new BusException("请扫描或输入正确的流转单二维码!");
                }
            }
            else
            {
                throw new BusException("请扫描流转单二维码!");
            }

        }


        /// <summary>
        /// 物料完工扫码流转卡生成收货单
        /// </summary>
        /// <param name="carculqrcode"></param>
        /// <returns></returns>
        public async Task<PB_CirculationBillPDADTO> GetPDACarCulDataListInfoDetail(string carculqrcode)
        {
            if (!carculqrcode.IsNullOrEmpty())
            {
                if (carculqrcode.Split(',')?[0].ToString() == "31")
                {
                    string carculid = carculqrcode.Split(',')?[1].ToString();
                    Expression<Func<PB_CirculationBill, PB_Dispatch, PB_DispatchDetail, PB_Material,PB_ProductionOrderDetail, PB_CirculationBillPDADTO>> select = (a, b, c, d,e) => new PB_CirculationBillPDADTO
                    {
                        StaffName = b.StaffName,
                        MaterialName = d.Name,
                        MaterialCode = d.Code,
                        DispatchDetailId = c.Id,
                        CompleteNums=e.CompleteNums == null?0:e.CompleteNums,
                        Nums=e.Productionquantity,
                        IsSingle=d.IsSingle,
                    };
                    select = select.BuildExtendSelectExpre();
                    var q = from a in Db.GetIQueryable<PB_CirculationBill>().AsExpandable()
                            join b in Db.GetIQueryable<PB_Dispatch>() on a.DispatchId equals b.Id into ab
                            from b in ab.DefaultIfEmpty()
                            join c in Db.GetIQueryable<PB_DispatchDetail>() on new { ParentId = b.Id, a.MaterialId } equals new { c.ParentId, c.MaterialId } into abc
                            from c in abc.DefaultIfEmpty()
                            join d in Db.GetIQueryable<PB_Material>() on c.SubordinateProduct equals d.Id into cd
                            from d in cd.DefaultIfEmpty()
                            join e in Db.GetIQueryable<PB_ProductionOrderDetail>() on a.TaskDetailId equals e.Id into ae
                            from e in ae.DefaultIfEmpty()
                            select @select.Invoke(a, b, c, d,e);
                    var qdata = await q.Where(x => x.Id == carculid).FirstOrDefaultAsync();
                    if (qdata == null)
                    {
                        throw new BusException("请扫描或输入正确的流转单二维码!");
                    }
                    if ((qdata.StatisticalQuantity+qdata.CompleteNums)>qdata.Nums) {
                        throw new BusException("流转卡统计数量大于生产任务单已生产数量");
                    }
                    else if (qdata.WorkerId.IsNullOrEmpty() || qdata.Statistician.IsNullOrEmpty())
                    {
                        throw new BusException("该流转卡未质检或统计");
                    }

                    else
                    {
                        return qdata;
                    }

                }
                else
                {
                    throw new BusException("请扫描或输入正确的流转单二维码!");
                }
            }
            else
            {
                throw new BusException("请扫描流转单二维码!");
            }

        }

        /// <summary>
        /// 确定领取
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        [Transactional]
        public async Task UpdateStockUpTrayDataAsync(DispatchDTO data)
        {
            if (data.carculqrcode.Split(',')?[0].ToString() == "31")
            {
                //获取操作人信息
                Base_User userdata = await Db.GetIQueryable<Base_User>().Where(x => x.Id == _operator.UserId).FirstOrDefaultAsync();
                string carculid = data.carculqrcode.Split(',')?[1].ToString();
                Expression<Func<PB_CirculationBill, PB_Dispatch, PB_DispatchDetail, PB_Material, PB_CirculationBillPDADTO>> select = (a, b, c, d) => new PB_CirculationBillPDADTO
                {
                    StaffName = b.StaffName,
                    MaterialName = d.Name,
                    MaterialCode = d.Code,
                    DispatchDetailId = c.Id
                };
                select = select.BuildExtendSelectExpre();
                var q = from a in Db.GetIQueryable<PB_CirculationBill>().AsExpandable()
                        join b in Db.GetIQueryable<PB_Dispatch>() on a.DispatchId equals b.Id into ab
                        from b in ab.DefaultIfEmpty()
                        join c in Db.GetIQueryable<PB_DispatchDetail>() on new { ParentId = b.Id, a.MaterialId } equals new { c.ParentId, c.MaterialId } into abc
                        from c in abc.DefaultIfEmpty()
                        join d in Db.GetIQueryable<PB_Material>() on c.MaterialId equals d.Id into cd
                        from d in cd.DefaultIfEmpty()
                        select @select.Invoke(a, b, c, d);
                var qdata = await q.Where(x => x.Id == carculid).FirstOrDefaultAsync();

                DispatchRemarkDTO dispatchRemark = new DispatchRemarkDTO()
                {
                    DispatchId = qdata.DispatchId,
                    DispatchDetailId = qdata.DispatchDetailId,
                    UserName = userdata.RealName,
                    ReceiveTime =DateTime.Now,
                    ReadyNums = data.Nums
                };
                string dispatchRemarkJson = dispatchRemark.ToJson();
                string sqlMains = "";
                //如果接受数量大于领取数量,修改领取数量并且写入json日志
                if ((qdata.ReceivingQuantity - Convert.ToDouble(qdata.ReadyNums)) > data.Nums)
                {
                    PB_CirculationBill data1 = await Db.GetIQueryable<PB_CirculationBill>().Where(s => s.Id == qdata.Id).FirstOrDefaultAsync();
                    List<string> props = new List<string>() { "ReadyNums", "DispatchRemark" };
                    data1.ReadyNums = data1.ReadyNums == null?data.Nums: data1.ReadyNums + data.Nums;
                    data1.DispatchRemark = data1.DispatchRemark + dispatchRemarkJson;
                    await Db.UpdateAsync(data1, props);
                }
                else if ((qdata.ReceivingQuantity - Convert.ToDouble(qdata.ReadyNums)) == data.Nums) // 如果等于,托盘上没有数量,删除托盘与生产派单的关联
                {
                    PB_CirculationBill data1 = await Db.GetIQueryable<PB_CirculationBill>().Where(s => s.Id == qdata.Id).FirstOrDefaultAsync();
                    List<string> props = new List<string>() { "ReadyNums", "DispatchRemark" };
                    data1.ReadyNums = data1.ReadyNums == null ? data.Nums : data1.ReadyNums + data.Nums;
                    data1.DispatchRemark = data1.DispatchRemark + dispatchRemarkJson;
                    await Db.UpdateAsync(data1, props);
                    sqlMains = $"DELETE FROM PB_TrayCirculationBill WHERE TrayId = '{data1.TrayId}' AND CirBillId = '{data1.Id}';";
                    await ExecuteSqlAsync(sqlMains);
                }
                else
                {
                    throw new BusException("托盘上所需物料不足,请重新扫描!");
                }

            }
            else
            {
                throw new BusException("请扫描流转单二维码!");
            }

            



        }

        /// <summary>
        /// 派工单作废操作
        /// </summary>
        /// <param name="Id">派工单ID</param>
        /// <returns></returns>
        public async Task TovoidDispatch(string Id)
        {
            string sqlmain = string.Empty;
            string sqlselect = "select * from PB_CirculationBill where  DispatchId ='"+Id+"'";
            var PB_CirculationBill =await Db.GetListBySqlAsync<PB_CirculationBill>(sqlselect);
            if (PB_CirculationBill.Count>0) {
                throw new BusException("该派工单已有报工禁止报废");
            }

            var pb_dispatch =await Db.GetIQueryable<PB_Dispatch>().Where(m=>m.Id==Id).FirstOrDefaultAsync();
            if (pb_dispatch.State>0) {
                if (pb_dispatch.State==2) {
                    sqlmain += "UPDATE PB_Dispatch SET State = 1  WHERE   id = '"+Id+"' ;";
                    sqlmain += "UPDATE `PB_CirculationBill` SET `DispatchRemark` = replace (`DispatchRemark`,'已补制,','')  WHERE Id='"+ pb_dispatch .CirBillId+ "'";
                    await Db.ExecuteSqlAsync(sqlmain);
                }
                else if(pb_dispatch.State==3){
                    sqlmain += "UPDATE PB_Dispatch SET State = 1  WHERE   id = '" + Id + "' ;";
                    sqlmain += "UPDATE `PB_CirculationBill` SET `DispatchRemark` = replace (`DispatchRemark`,'已改制,','')  where Id='"+ pb_dispatch .CirBillId+ "' ";
                    await Db.ExecuteSqlAsync(sqlmain);
                }
            }
            else {
                StringBuilder sql = new StringBuilder();
                sql.AppendLine("UPDATE PB_Dispatch");
                sql.AppendLine("SET State = 1");
                sql.AppendLine("WHERE");
                sql.AppendLine($"id = '{Id}'");
                await ExecuteSqlAsync(sql.ToString());
            }

         
        }
        /// <summary>
        /// 大屏显示当日出勤班次
        /// </summary>
        /// <param name="Day"></param>
        /// <returns></returns>
        //        public ScreenDisplayValue3DTO GetTodayDispatchAttendNums(int Day)
        //        {
        //            string strSQL = @"SELECT COUNT(DISTINCT d.Id) AS value
        //FROM PB_Dispatch d 
        //JOIN PB_CirculationBill cb ON cb.DispatchId=d.Id AND d.StaffId=cb.WorkerId AND TO_DAYS(CURDATE())-TO_DAYS(DispatchDate)=" + Day.ToString();
        //            var res = Db.GetListBySqlAsync<ScreenDisplayValue2DTO>(strSQL).Result.FirstOrDefault();
        //            if (res != null)
        //            {
        //                return new Entity.DTO.ScreenDisplayValue3DTO { value = res.value.ToString() + "班次" };
        //            }
        //                return new Entity.DTO.ScreenDisplayValue3DTO { value = "0" + "班次" };            
        //        }
        //        /// <summary>
        //        /// 大屏显示当日出勤率
        //        /// </summary>
        //        /// <param name="Day"></param>
        //        /// <returns></returns>
        //        public ScreenDisplayDashboardDTO GetDispatchAttendRate(int Day)
        //        {
        //            string strSQL = @"SELECT COUNT(Id) AS value FROM PB_Dispatch 
        //WHERE TO_DAYS(CURDATE())-TO_DAYS(DispatchDate)="+Day.ToString();
        //            int total = Db.GetListBySqlAsync<ScreenDisplayValue2DTO>(strSQL).Result.FirstOrDefault().value;
        //            strSQL = @"SELECT COUNT(DISTINCT d.Id) AS value
        //FROM PB_Dispatch d 
        //JOIN PB_CirculationBill cb ON cb.DispatchId=d.Id AND d.StaffId=cb.WorkerId AND TO_DAYS(CURDATE())-TO_DAYS(DispatchDate)="+Day.ToString();
        //            int attend = Db.GetListBySqlAsync<ScreenDisplayValue2DTO>(strSQL).Result.FirstOrDefault().value;
        //            ScreenDisplayDashboardDTO res = new ScreenDisplayDashboardDTO();
        //            if (total!= 0)
        //            {
        //                res.value = (decimal)attend/(decimal)total *100;
        //                res.value = Convert.ToDecimal(res.value.ToString("#0.##"));
        //            }
        //            else
        //            {
        //                res.value = 0;
        //            }
        //            return res;
        //        }
        //        /// <summary>
        //        /// 大屏显示七日出勤班次
        //        /// </summary>
        //        /// <param name="Day">前第几天</param>
        //        /// <returns>七日出勤班次</returns>
        //        public PB_Dispatch7DayAttendDTO GetSevenDaysStatisticsNums(int Day)
        //        {
        //            PB_Dispatch7DayAttendDTO res = new PB_Dispatch7DayAttendDTO();
        //            string strSQL = @"SELECT  '一' AS name,COUNT(DISTINCT d.Id) AS value
        //FROM PB_Dispatch d 
        //JOIN PB_CirculationBill cb ON cb.DispatchId=d.Id AND d.StaffId=cb.WorkerId AND TO_DAYS(CURDATE())-TO_DAYS(DispatchDate)=" + (6 + Day).ToString();
        //             var temp = Db.GetListBySql<ScreenDisplayValue4DTO>(strSQL).FirstOrDefault();
        //            //保留两位小数
        //            res.list.Add(temp);
        //            strSQL = @"SELECT  '二' AS name,COUNT(DISTINCT d.Id) AS value
        //FROM PB_Dispatch d 
        //JOIN PB_CirculationBill cb ON cb.DispatchId=d.Id AND d.StaffId=cb.WorkerId AND TO_DAYS(CURDATE())-TO_DAYS(DispatchDate)=" + (5 + Day).ToString();
        //             temp = Db.GetListBySql<ScreenDisplayValue4DTO>(strSQL).FirstOrDefault();
        //            //保留两位小数
        //            res.list.Add(temp);
        //            strSQL = @"SELECT  '三' AS name,COUNT(DISTINCT d.Id) AS value
        //FROM PB_Dispatch d 
        //JOIN PB_CirculationBill cb ON cb.DispatchId=d.Id AND d.StaffId=cb.WorkerId AND TO_DAYS(CURDATE())-TO_DAYS(DispatchDate)=" + (4 + Day).ToString();
        //             temp = Db.GetListBySql<ScreenDisplayValue4DTO>(strSQL).FirstOrDefault();
        //            //保留两位小数
        //            res.list.Add(temp);
        //            strSQL = @"SELECT  '四' AS name,COUNT(DISTINCT d.Id) AS value
        //FROM PB_Dispatch d 
        //JOIN PB_CirculationBill cb ON cb.DispatchId=d.Id AND d.StaffId=cb.WorkerId AND TO_DAYS(CURDATE())-TO_DAYS(DispatchDate)=" + (3 + Day).ToString();
        //             temp = Db.GetListBySql<ScreenDisplayValue4DTO>(strSQL).FirstOrDefault();
        //            //保留两位小数
        //            res.list.Add(temp);
        //            strSQL = @"SELECT  '五' AS name,COUNT(DISTINCT d.Id) AS value
        //FROM PB_Dispatch d 
        //JOIN PB_CirculationBill cb ON cb.DispatchId=d.Id AND d.StaffId=cb.WorkerId AND TO_DAYS(CURDATE())-TO_DAYS(DispatchDate)=" + (2 + Day).ToString();
        //             temp = Db.GetListBySql<ScreenDisplayValue4DTO>(strSQL).FirstOrDefault();
        //            //保留两位小数
        //            res.list.Add(temp);
        //            strSQL = @"SELECT  '六' AS name,COUNT(DISTINCT d.Id) AS value
        //FROM PB_Dispatch d 
        //JOIN PB_CirculationBill cb ON cb.DispatchId=d.Id AND d.StaffId=cb.WorkerId AND TO_DAYS(CURDATE())-TO_DAYS(DispatchDate)=" + (1 + Day).ToString();
        //             temp = Db.GetListBySql<ScreenDisplayValue4DTO>(strSQL).FirstOrDefault();
        //            //保留两位小数
        //            res.list.Add(temp);
        //            strSQL = @"SELECT  '七' AS name,COUNT(DISTINCT d.Id) AS value
        //FROM PB_Dispatch d 
        //JOIN PB_CirculationBill cb ON cb.DispatchId=d.Id AND d.StaffId=cb.WorkerId AND TO_DAYS(CURDATE())-TO_DAYS(DispatchDate)=" + (0 + Day).ToString();
        //             temp = Db.GetListBySql<ScreenDisplayValue4DTO>(strSQL).FirstOrDefault();

        //            res.list.Add(temp);
        //            for (int i = 6; i > -1; i--)
        //            {
        //                res.list[6-i].name = DateTime.Now.AddDays(-1 * i).ToString("MM.dd");
        //            }
        //            return res;
        //        }

        #endregion

        #region 私有成员

        #endregion

        #region

        /// <summary>
        /// 派工单选择工件
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageResult<DispatchMaterialDTO>> GetDispatchMaterialListAsync(PageInput<DispatchMaterialConditionDTO> input)
        {
            var search = input.Search;
            if (search.IsProduct == false)
            {
                Expression<Func<PB_Material, PB_ProductionOrderDetail, DispatchMaterialDTO>> select = (a, b) => new DispatchMaterialDTO
                {
                    MaterialId = a.Id,
                    Name = a.Name,
                    Code = a.Code,
                    Spec = a.Spec,
                    Length = a.Length,
                    Width = a.Width,
                    Height = a.Height,
                    OrderDetailId = b.Id,
                    IsProduct = a.IsProduct,
                    MaterialTypeId = a.MaterialTypeId,
                    DeptId = a.DeptId,
                    OrderId = b.ParentId
                };
                select = select.BuildExtendSelectExpre();
                var q = from a in Db.GetIQueryable<PB_Material>().AsExpandable()
                        join b in Db.GetIQueryable<PB_ProductionOrderDetail>() on a.Id equals b.MaterialId into ab
                        from b in ab.DefaultIfEmpty()
                        select @select.Invoke(a, b);

                var where = LinqHelper.True<DispatchMaterialDTO>();
                //筛选
                if (!search.Keyword.IsNullOrEmpty())
                {
                    var newWhere = DynamicExpressionParser.ParseLambda<DispatchMaterialDTO, bool>(
                        ParsingConfig.Default, false, $@"Name.Contains(@0) or Code.Contains(@0)", search.Keyword);
                    where = where.And(newWhere);
                }
                if (!search.MaterialTypeId.IsNullOrEmpty())
                {
                    List<string> liststr = new List<string>();
                    List<PB_MaterialType> listdata = await Db.GetIQueryable<PB_MaterialType>().ToListAsync();
                    var typelist = listdata.Where(x => x.Id == search.MaterialTypeId).FirstOrDefault();
                    if (typelist != null)
                    {
                        var typelist2 = listdata.Where(x => x.ParentId == typelist.Id).ToList();
                        if (typelist2.Count > 0)
                        {

                            foreach (var item in typelist2)
                            {
                                liststr.Add(item.Id);
                                GetItem(item.Id, liststr, listdata);
                            }
                            q = q.Where(w => liststr.Contains(w.MaterialTypeId));
                        }
                        else
                        {
                            q = q.Where(w => w.MaterialTypeId == search.MaterialTypeId);
                        }
                    }
                }
                if (!search.IsProduct.IsNullOrEmpty())
                {
                    where = where.And(x => x.IsProduct == false);
                }
                //物料信息增加数据过滤
                q = q.Where(m => _operator.Property.DeptIds.Contains(m.DeptId));
                return await q.Where(where).GetPageResultAsync(input);
            }
            else
            {
                Expression<Func<PB_ProductionOrderDetail, PB_Material, DispatchMaterialDTO>> select = (a, b) => new DispatchMaterialDTO
                {
                    MaterialId = b.Id,
                    Name = b.Name,
                    Code = b.Code,
                    Spec = b.Spec,
                    Length = b.Length,
                    Width = b.Width,
                    Height = b.Height,
                    OrderDetailId = a.Id,
                    IsProduct = b.IsProduct,
                    MaterialTypeId = b.MaterialTypeId,
                    DeptId = b.DeptId,
                    OrderId = a.ParentId
                };
                select = select.BuildExtendSelectExpre();
                var q = from a in Db.GetIQueryable<PB_ProductionOrderDetail>().AsExpandable()
                        join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
                        from b in ab.DefaultIfEmpty()
                        select @select.Invoke(a, b);

                var where = LinqHelper.True<DispatchMaterialDTO>();
                //筛选
                if (!search.Keyword.IsNullOrEmpty())
                {
                    var newWhere = DynamicExpressionParser.ParseLambda<DispatchMaterialDTO, bool>(
                        ParsingConfig.Default, false, $@"Name.Contains(@0) or Code.Contains(@0)", search.Keyword);
                    where = where.And(newWhere);
                }
                if (!search.MaterialTypeId.IsNullOrEmpty())
                {
                    List<string> liststr = new List<string>();
                    List<PB_MaterialType> listdata = await Db.GetIQueryable<PB_MaterialType>().ToListAsync();
                    var typelist = listdata.Where(x => x.Id == search.MaterialTypeId).FirstOrDefault();
                    if (typelist != null)
                    {
                        var typelist2 = listdata.Where(x => x.ParentId == typelist.Id).ToList();
                        if (typelist2.Count > 0)
                        {

                            foreach (var item in typelist2)
                            {
                                liststr.Add(item.Id);
                                GetItem(item.Id, liststr, listdata);
                            }
                            q = q.Where(w => liststr.Contains(w.MaterialTypeId));
                        }
                        else
                        {
                            q = q.Where(w => w.MaterialTypeId == search.MaterialTypeId);
                        }
                    }
                }
                if (!search.IsProduct.IsNullOrEmpty())
                {
                    where = where.And(x => x.IsProduct == true);
                }
                if (!search.OrderId.IsNullOrEmpty())
                {
                    where = where.And(x => x.OrderId == search.OrderId);
                }
                //物料信息增加数据过滤
                q = q.Where(m => _operator.Property.DeptIds.Contains(m.DeptId));
                return await q.Where(where).GetPageResultAsync(input);
            }
        }

        //递归查询物料类型下的子类型
        private List<string> GetItem(string typeId, List<string> liststr, List<PB_MaterialType> listdata)
        {
            var itemlist = listdata.Where(x => x.ParentId == typeId).ToList();
            foreach (var item in itemlist)
            {
                liststr.Add(item.Id);
                GetItem(item.Id, liststr, listdata);
            }
            return liststr;
        }

        /// <summary>
        /// 派工单选择隶属成品
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageResult<OrderMaterialDTO>> GetOrderMaterialListAsync(PageInput<DispatchMaterialConditionDTO> input)
        {
            var search = input.Search;
            Expression<Func<PB_ProductionOrderDetail, PB_Material, OrderMaterialDTO>> select = (a, b) => new OrderMaterialDTO
            {
                OrderId = a.ParentId,
                OrderDetailId = a.Id,
                MaterialId = a.MaterialId,
                MaterName = b.Name,
                MaterCode = b.Code,
                MaterSpec = b.Spec,
                Nums = a.Nums,
                Productionquantity = a.Productionquantity,
                CompleteNums = a.CompleteNums
            };
            var where = LinqHelper.True<OrderMaterialDTO>();
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<PB_ProductionOrderDetail>().AsExpandable()
                    join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    select @select.Invoke(a, b);
            //筛选
            if (!search.Keyword.IsNullOrEmpty())
            {
                var newWhere = DynamicExpressionParser.ParseLambda<OrderMaterialDTO, bool>(
                    ParsingConfig.Default, false, $@"MaterName.Contains(@0) or MaterCode.Contains(@0)", search.Keyword);
                where = where.And(newWhere);
            }
            where = where.And(x => x.OrderId == search.OrderId);
            return await q.Where(where).GetPageResultAsync(input);

        }
        public async Task<AjaxResult> SaveVerificationDispatch(VerificationDispatch data)
        {
            int Return = 0;
            foreach (var item in data.Detaillist)
            {
                foreach (var items in item.DetailMater)
                {
                    var sql = "select * from PB_DispatchDetail as a LEFT JOIN PB_Dispatch AS b on a.ParentId = b.Id WHERE a.OrderDetailId = '"+item.OrderDetailId+"' AND a.MaterialId = '"+items.MaterialId+"' AND a.SubId = '"+items.SubId+"' AND a.SubordinateProduct = '"+items.SubordinateProduct+"' AND b.State = 0";
                    var PB_DispatchDetaillist  =await Db.GetListBySqlAsync<PB_DispatchDetail>(sql);
                    if (PB_DispatchDetaillist.Count>0) {
                        Return++;
                    }
                }
            } 
            if (Return > 0)
            {
                AjaxResult res = new AjaxResult
                {
                    Success = false,
                    Msg = "是否继续派工?"
                };

                return res;
            }
            else
            {
                AjaxResult res = new AjaxResult
                {
                    Success = true,
                    Msg = "请求成功！"
                };

                return res;
            }
        }


        #endregion

        #region  快速派工

        /// <summary>
        /// 快速派工保存
        /// </summary>
        /// <param name="data"></param>
        /// <param name="_redisOptions"></param>
        /// <returns></returns>
        [Transactional]
        public async Task SaveQuickDispatchAsync(QuickDispatch data, RedisOptions _redisOptions)
        {
            Base_User userdata = await Db.GetIQueryable<Base_User>().Where(x => x.Id == _operator.UserId).FirstOrDefaultAsync();
            //获取任务单Id
            string orderId = data.OrderId;
            //获取任务类型
            int? IsRefurbishment = data.IsRefurbishment;
            //项目Id
            string ProjectId = data.ProjectId;

            PB_ProductionOrder ProductionOrder = await Db.GetIQueryable<PB_ProductionOrder>().Where(x => x.Id == orderId).FirstOrDefaultAsync();
            if (ProductionOrder.IsRefurbishment == 4)
            {
                //按物料分组求和
                var datagroup = data.SubordinateProductList.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, Nums = x.Sum(i => i.Nums) }).ToList();
                Expression<Func<PB_ProductionOrderDetail, PB_Material, ProductionOrderDetailDTO>> select = (a, b) => new ProductionOrderDetailDTO
                {
                    OrderId = a.ParentId,
                    OrderDetailId = a.Id,
                    SubordinateProduct = a.MaterialId,
                    SubordinateProductName = b.Name,
                    Productionquantity = a.Productionquantity,
                    ProductCode = b.ProductCode,
                    Spec = b.Spec,
                    Ofmaterial = b.Ofmaterial
                };
                select = select.BuildExtendSelectExpre();
                var q = from a in Db.GetIQueryable<PB_ProductionOrderDetail>().AsExpandable()
                        join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
                        from b in ab.DefaultIfEmpty()
                        select @select.Invoke(a, b);
                List<ProductionOrderDetailDTO> OrderDetailList = await q.Where(x => x.OrderId == orderId).ToListAsync();
                List<PB_DispatchDetail> Detaillist = await Db.GetIQueryable<PB_DispatchDetail>().Where(x => x.OrderId == orderId).ToListAsync();
                var DispatchGroup = Detaillist.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, Nums = x.Sum(i => i.Nums) }).ToList();
                foreach (var item in OrderDetailList)
                {
                    var itemdata = datagroup.Where(x => x.MaterialId == item.SubordinateProduct).FirstOrDefault();
                    var dedata = DispatchGroup.Where(x => x.MaterialId == item.SubordinateProduct).FirstOrDefault();
                    if (!itemdata.IsNullOrEmpty())
                    {
                        if (itemdata.Nums > (item.Productionquantity - (dedata.IsNullOrEmpty() ? 0 : dedata.Nums)))
                        {
                            throw new BusException($"{item.SubordinateProductName}派工数量大于生产任务单未派工数量");
                        }
                    }
                }

            }

            List<PB_Dispatch> Dispatchlist = new List<PB_Dispatch>();
            List<PB_DispatchDetail> DispatchDetaillist = new List<PB_DispatchDetail>();
            //循环获取人员信息,人员信息去重
            var userlist = data.SubordinateProductList.GroupBy(x => new { x.StaffId, x.StaffName, x.TeamType, x.DispatchDate }).Select(x => new { StaffId = x.Key.StaffId, StaffName = x.Key.StaffName, TeamType = x.Key.TeamType, DispatchDate = x.Key.DispatchDate }).ToList();
            foreach (var item in userlist)
            {
                PB_Dispatch Dispatch = new PB_Dispatch();
                string DispatchId = IdHelper.GetId();
                Dispatch.Id = DispatchId;
                Dispatch.StaffId = item.StaffId;
                Dispatch.StaffName = item.StaffName;
                #region  获取工单号
                //实例化Redis操作类
                string workorderNo = "";
                _redisOptions.DbChannel = (int)Redis_Type.redis6;
                RedisSetHelper redis = new RedisSetHelper(_redisOptions);
                if (RedisSetHelper.Exists("DispatchNumber"))
                {
                    string thedate = DateTime.Now.ToString("yyyy-MM-dd");
                    if (redis.GetString("DispatchTime") == thedate)
                    {
                        redis.SetString("DispatchNumber", (Convert.ToInt32(redis.GetString("DispatchNumber")) + 1).ToString());
                        workorderNo = "D_" + DateTime.Now.ToString("yyyyMMdd") + "_" + redis.GetString("DispatchNumber").PadLeft(4, '0');
                    }
                    else
                    {
                        redis.SetString("DispatchTime", thedate);
                        redis.SetString("DispatchNumber", "1");
                        workorderNo = "D_" + DateTime.Now.ToString("yyyyMMdd") + "_" + redis.GetString("DispatchNumber").PadLeft(4, '0');
                    }
                }
                else
                {
                    redis.SetString("DispatchNumber", "0");
                    redis.SetString("DispatchNumber", (Convert.ToInt32(redis.GetString("DispatchNumber")) + 1).ToString());
                    workorderNo = "D_" + DateTime.Now.ToString("yyyyMMdd") + "_" + redis.GetString("DispatchNumber").PadLeft(4, '0');
                }
                #endregion
                Dispatch.WorkOrderNo = workorderNo;
                Dispatch.DeptId = userdata.DepartmentId;
                Dispatch.TeamType = item.TeamType;
                Dispatch.DispatchDate = item.DispatchDate;
                Dispatch.CreatorId = _operator.UserId;
                Dispatch.CreateTime = DateTime.Now;
                Dispatch.CreatorRealName = userdata.RealName;
                Dispatch.Deleted = false;
                Dispatch.QrCode = h_Dispatch + "," + DispatchId;
                Dispatch.State = 0;
                Dispatchlist.Add(Dispatch);
                var itemdata = data.SubordinateProductList.Where(x => x.StaffId == item.StaffId && x.DispatchDate == item.DispatchDate).ToList();
                foreach (var items in itemdata)
                {
                    PB_DispatchDetail DispatchDetail = new PB_DispatchDetail();
                    string DetailId = IdHelper.GetId();
                    DispatchDetail.Id = DetailId;
                    DispatchDetail.ParentId = DispatchId;
                    DispatchDetail.IsRefurbishment = IsRefurbishment;
                    DispatchDetail.SubId = items.SubId;
                    DispatchDetail.ProjectId = ProjectId;
                    DispatchDetail.MaterialId = items.MaterialId;
                    DispatchDetail.OrderId = orderId;
                    DispatchDetail.OrderDetailId = items.OrderDetailId;
                    DispatchDetail.Aperture = items.Aperture;
                    DispatchDetail.Texture = items.Texture;
                    DispatchDetail.Nums = items.Nums;
                    DispatchDetail.QrCode = h_DispatchDetail + "," + DetailId;
                    DispatchDetail.CreatorId = _operator.UserId;
                    DispatchDetail.CreateTime = DateTime.Now;
                    DispatchDetail.Deleted = false;
                    DispatchDetail.CreatorRealName = userdata.RealName;
                    DispatchDetail.WorkReportQuantity = 0;
                    DispatchDetail.ReadyNums = 0;
                    DispatchDetail.SubordinateProduct = items.SubordinateProduct;
                    DispatchDetail.MaterialLength = items.MaterialLength;
                    DispatchDetail.PunchingNums = items.PunchingNums;
                    DispatchDetail.PunchingPosition = items.PunchingPosition;
                    DispatchDetail.ModeType = items.ModeType;
                    DispatchDetaillist.Add(DispatchDetail);
                }
            }
            await Db.InsertAsync<PB_DispatchDetail>(DispatchDetaillist);
            await Db.InsertAsync<PB_Dispatch>(Dispatchlist);
        }

        /// <summary>
        /// 快速派工获取任务单明细
        /// </summary>
        /// <param name="OrderId">任务单Id</param>
        /// <returns></returns>
        public async Task<List<ProductionOrderDetailDTO>> GetProductionOrderDetailAsync(string OrderId)
        {
            PB_ProductionOrder ProductionOrder = await Db.GetIQueryable<PB_ProductionOrder>().Where(x => x.Id == OrderId).FirstOrDefaultAsync();
            if (ProductionOrder.IsRefurbishment != 4)
            {
                Expression<Func<PB_ProductionOrderDetail, PB_Material, ProductionOrderDetailDTO>> select = (a, b) => new ProductionOrderDetailDTO
                {
                    OrderId = a.ParentId,
                    OrderDetailId = a.Id,
                    SubordinateProduct = a.MaterialId,
                    SubordinateProductName = b.Name,
                    Productionquantity = a.Productionquantity,
                    ProductCode = b.ProductCode,
                    Spec = b.Spec,
                    Ofmaterial = b.Ofmaterial
                };
                select = select.BuildExtendSelectExpre();
                var q = from a in Db.GetIQueryable<PB_ProductionOrderDetail>().AsExpandable()
                        join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
                        from b in ab.DefaultIfEmpty()
                        select @select.Invoke(a, b);
                return await q.Where(x => x.OrderId == OrderId).ToListAsync();
            }
            else
            {
                string sqlMain = $@"select * from (
select a.ParentId as OrderId,a.Id as OrderDetailId,a.MaterialId as SubordinateProduct,b.`Name` as SubordinateProductName,b.ProductCode,b.Spec,b.Ofmaterial,(a.Productionquantity-IFNULL(c.Nums,0)) as Productionquantity from pb_productionorderdetail as a
left join pb_material as b on a.MaterialId=b.Id
left join (select OrderDetailId,MaterialId,SUM(Nums) as Nums from pb_dispatchdetail where OrderId='{OrderId}' group by OrderDetailId,MaterialId) as c on a.Id=c.OrderDetailId and a.MaterialId=c.MaterialId
where a.ParentId='{OrderId}') as t where Productionquantity>0";
                return await Db.GetListBySqlAsync<ProductionOrderDetailDTO>(sqlMain);
            }
        }

        /// <summary>
        /// 快速派工根据成品获取半成品信息
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageResult<OrderMaterialProductDTO>> GetOrderMaterialProductAsync(PageInput<OrderMaterialProductConditionDTO> input)
        {
            var where = LinqHelper.True<OrderMaterialProductDTO>();
            var search = input.Search;
            PageResult<OrderMaterialProductDTO> res = new PageResult<OrderMaterialProductDTO>();
            string sql = string.Empty;
            if (search.IsProduct == false)
            {
                //Expression<Func<PB_MaterialProduct, PB_Material, Base_UnitItems, OrderMaterialProductDTO>> select = (a, b, c) => new OrderMaterialProductDTO
                //{
                //    SubordinateProduct = a.SubordinateProduct,
                //    MaterialId = a.MaterialId,
                //    MaterialName = b.Name,
                //    MaterialCode = b.Code,
                //    MaterialSpec = b.Spec,
                //    productionId = b.productionId,
                //    productionName = c.Name,
                //    Weight = b.Weight,
                //    ProcecureId = b.ProcecureId,
                //    Length = b.Length,
                //    IsProduct = b.IsProduct
                //};
                //select = select.BuildExtendSelectExpre();
                //var q = from a in Db.GetIQueryable<PB_MaterialProduct>().AsExpandable()
                //        join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
                //        from b in ab.DefaultIfEmpty()
                //        join c in Db.GetIQueryable<Base_UnitItems>() on b.productionId equals c.Id into bc
                //        from c in bc.DefaultIfEmpty()
                //        select @select.Invoke(a, b, c);
                //if (!search.Keyword.IsNullOrEmpty())
                //{
                //    var newWhere = DynamicExpressionParser.ParseLambda<OrderMaterialProductDTO, bool>(
                //       ParsingConfig.Default, false, $@"MaterialName.Contains(@0) or MaterialCode.Contains(@0)", search.Keyword);
                //    where = where.And(newWhere);
                //}
                //where = where.And(x => x.SubordinateProduct == search.SubordinateProduct);
                //res = await q.Where(where).GetPageResultAsync(input);
                string sqlMain = @"select a.Id as SubordinateProduct,a.Id as MaterialId,a.`Name` as MaterialName,a.`Code` as MaterialCode,a.Spec as MaterialSpec,a.productionId,b.`Name` as productionName,a.Weight,a.ProcecureId,a.Length,a.IsProduct,a.IsVirtualkey,a.UsageStatus,IFNULL(c.DispatchNums,0)  as DispatchNums  from PB_Material as a
                  left join Base_UnitItems as b on a.productionId=b.Id
                  left join (select MaterialId,SUM(Nums) as DispatchNums from PB_DispatchDetail LEFT JOIN PB_Dispatch AS dispatch on  PB_DispatchDetail.ParentId=dispatch.Id where OrderId='{0}' AND dispatch.State=0   group by MaterialId) as c on a.Id=c.MaterialId
                  where a.IsVirtualkey=1 ";
                if (!search.Keyword.IsNullOrEmpty())
                {
                    sqlMain += @" and (a.`Name` like '%{1}%' or a.`Code` like '%{1}%')";
                }
                sql = string.Format(sqlMain, search.OrderId, search.Keyword);
            }
            else if (search.IsProduct == true)
            {
                string sqlMain = @"select a.Id as SubordinateProduct,a.Id as MaterialId,a.`Name` as MaterialName,a.`Code` as MaterialCode,a.Spec as MaterialSpec,a.productionId,b.`Name` as productionName,a.Weight,a.ProcecureId,a.Length,a.IsProduct,a.IsVirtualkey,a.UsageStatus,IFNULL(c.DispatchNums,0)  as DispatchNums  from PB_Material as a
                  left join Base_UnitItems as b on a.productionId=b.Id
                  left join (select MaterialId,SUM(Nums) as DispatchNums from PB_DispatchDetail LEFT JOIN PB_Dispatch AS dispatch on  PB_DispatchDetail.ParentId=dispatch.Id where OrderId='{0}'  AND dispatch.State=0 group by MaterialId) as c on a.Id=c.MaterialId
                  where a.IsProduct=1 and a.Id='{2}' ";
                if (!search.Keyword.IsNullOrEmpty())
                {
                    sqlMain += @" and (a.`Name` like '%{1}%' or a.`Code` like '%{1}%')";
                }
                sql = string.Format(sqlMain, search.OrderId, search.Keyword, search.SubordinateProduct);
            }
            var lidata = await Db.GetListBySqlAsync<OrderMaterialProductDTO>(sql);
            int num = lidata.Count;
            var list = lidata
               .Skip((input.PageIndex - 1) * input.PageRows)
               .Take(input.PageRows).ToList();
            return new PageResult<OrderMaterialProductDTO> { Data = list, Total = num };
        }

        /// <summary>
        /// 获取排班班组
        /// </summary>
        /// <param name="WorkShopId">车间Id</param>
        /// <returns></returns>
        public async Task<List<PB_ShiftType>> GetShiftTypeListAsync(string WorkShopId)
        {
            List<PB_ShiftType> listata = await Db.GetIQueryable<PB_ShiftType>().Where(x => x.WorkShopID == WorkShopId).ToListAsync();
            return listata;
        }

        /// <summary>
        /// 快速派工获取排班明细信息
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageResult<ScheduleDetailDTO>> GetScheduleDetailAsync(PageInput<ScheduleDetailConditionDTO> input)
        {
            var search = input.Search;
            string WorkShopId = _operator.Property?.DepartmentId;
            string sqlMain = @"select a.Id,a.UserId,d.RealName,a.Type,b.Name as TypeName,b.WorkShopID,e.`Name` as WorkShopName,a.WorkDate,role.RoleId roleId  from PB_ScheduleDetail as a left join PB_ShiftType as b on a.Type=b.Id left join PB_Schedule as c on a.ParentId=c.Id left join Base_User as d on a.UserId=d.Id LEFT join Base_Department as e on b.WorkShopID=e.Id left JOIN Base_UserRole AS role on d.ID= role.UserId where c.Toexamine=2 ";
            if (!search.WorkShopId.IsNullOrEmpty())
            {
                sqlMain += @"and b.WorkShopID='" + search.WorkShopId + "' ";
            }
            if (!search.WorkDate.IsNullOrEmpty())
            {
                sqlMain += @"and a.WorkDate='" + search.WorkDate + "' ";
            }
            if (!search.TypeId.IsNullOrEmpty())
            {
                sqlMain += @"and a.Type='" + search.TypeId + "' ";
            }
            if (!search.Keyword.IsNullOrEmpty())
            {
                sqlMain += @"and d.RealName like '%" + search.Keyword + "%' ";
            }
            
            if (!search.Roid.IsNullOrEmpty()) {
                if (search.Roid.Count>0) {
                    string roid = "";
                    foreach (var item in search.Roid) {
                        roid += item + "'" + "," + "'";

                    }
                    roid = roid.Substring(0, roid.Length - 3);
                    sqlMain += @" and (role.RoleId in('" + roid + "'))";
                }
            }
            List<ScheduleDetailDTO> listdata = await Db.GetListBySqlAsync<ScheduleDetailDTO>(sqlMain);
            int num = listdata.Count;
            var list = listdata
               .Skip((input.PageIndex - 1) * input.PageRows)
               .Take(input.PageRows).ToList();
            return new PageResult<ScheduleDetailDTO> { Data = list, Total = num };
        }


        /// <summary>
        /// 获取任务单信息
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageResult<Entity.PB.PB_ProductionOrderDTO>> GetProductionOrderAsync(PageInput<PB_ProductionOrderConditionDTO> input)
        {
            var search = input.Search;
            var where = LinqHelper.True<Entity.PB.PB_ProductionOrderDTO>();
            Expression<Func<PB_ProductionOrder, PB_DesignList, Base_Department, Base_User, PB_Project, PB_Buildingnumber, Entity.PB.PB_ProductionOrderDTO>> select = (a, b, c, d, e, f) => new Entity.PB.PB_ProductionOrderDTO
            {
                ProjectName = e.Name + f.Buildingnumber,
                DLName = b.Name,
                DeptName = c.Name,
                UserName = d.RealName,
                ProjectId = b.ProjectId
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<PB_ProductionOrder>().AsExpandable()
                    join b in Db.GetIQueryable<PB_DesignList>() on a.DesignId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<Base_Department>() on a.DeptId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<Base_User>() on a.CreatorId equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    join e in Db.GetIQueryable<PB_Project>() on b.ProjectId equals e.Id into be
                    from e in be.DefaultIfEmpty()
                    join f in Db.GetIQueryable<PB_Buildingnumber>() on b.BuildingNumberID equals f.Id into bf
                    from f in bf.DefaultIfEmpty()
                    select @select.Invoke(a, b, c, d, e, f);

            //筛选
            if (!search.Keyword.IsNullOrEmpty())
            {
                var newWhere = DynamicExpressionParser.ParseLambda<Entity.PB.PB_ProductionOrderDTO, bool>(
                    ParsingConfig.Default, false, $@"ProjectName.Contains(@0) or POName.Contains(@0)", search.Keyword);
                where = where.And(newWhere);
            }
            if (!search.StartDate.IsNullOrEmpty() && !search.EndDate.IsNullOrEmpty())
            {
                DateTime StartDate = Convert.ToDateTime(Convert.ToDateTime(search.StartDate).Date.ToString());
                DateTime EndDate = Convert.ToDateTime(Convert.ToDateTime(search.EndDate).Date.ToString()).AddDays(1).AddSeconds(-1);
                where = where.And(x => StartDate <= x.BeginDate && x.EndDate <= EndDate);
            }
            if (!search.IsRefurbishment.IsNullOrEmpty())
            {
                where = where.And(x => x.IsRefurbishment == search.IsRefurbishment);
            }
            where = where.And(x => x.Status == 1);
            //生产订单 权限
            where = where.And(m => _operator.Property.DeptIds.Contains(m.DeptId));
            return await q.Where(where).GetPageResultAsync(input);
        }


        #endregion

        #region  原材料领料

        /// <summary>
        /// 原材料领料获取物料信息
        /// </summary>
        /// <param name="MaterialQrCode">物料二维码</param>
        /// <param name="LocaQrCode">货位二维码</param>
        /// <returns></returns>
        [Transactional]
        public async Task<RawStorMaterialDTO> GetRawMaterialDataAsync(string MaterialQrCode, string LocaQrCode)
        {
            string MaterialId = string.Empty;
            var model = MaterialQrCode.Split(',');
            if (model.Length > 2)
            {
                MaterialId = model?[3].ToString();
            }
            else
            {
                MaterialId = model?[1].ToString();
            }
            string locaId = LocaQrCode.Split(',')?[1].ToString();
            //获取派工单明细信息
            Expression<Func<IT_LocalMaterial, PB_Material, RawStorMaterialDTO>> select = (a, b) => new RawStorMaterialDTO
            {
                MaterialId = a.MaterialId,
                LocalId = a.LocalId,
                MaterialName = b.Name,
                MaterialCode = b.Code,
                MaterialSpec = b.Spec,
                Nums = a.ScanNum
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<IT_LocalMaterial>().AsExpandable()
                    join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    select @select.Invoke(a, b);
            var materdata = await q.Where(x => x.MaterialId == MaterialId && x.LocalId == locaId).FirstOrDefaultAsync();
            if (materdata != null)
            {
                if (materdata.Nums > 0)
                {
                    return materdata;
                }
                else
                {
                    throw new BusException("请检查物料是否还有库存");
                }
            }
            else
            {
                throw new BusException("请查询仓库物料是否存在");
            }

        }

        /// <summary>
        /// 原材料领料保存
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        [Transactional]
        public async Task SaveRawMaterialPickingAsync(RawMaterialDTO data)
        {
            //派工单明细Id
            string DispatchDetailId = data.DispatchDetailQrCode.Split(',')?[1].ToString();

            //货位Id
            string LocalId = data.LocaQrCode.Split(',')?[1].ToString();

            //查询货位上的原材料是否满足领料需求
            IT_LocalMaterial LocalMaterial = await Db.GetIQueryable<IT_LocalMaterial>().Where(x => x.LocalId == LocalId && x.MaterialId == data.MaterialId).FirstOrDefaultAsync();
            if (LocalMaterial.ScanNum < data.Nums)
            {
                throw new BusException("请检查货位上的物料数量是否满足领取数量");
            }

            //获取登录人信息
            Base_User userdata = await Db.GetIQueryable<Base_User>().Where(x => x.Id == _operator.UserId).FirstOrDefaultAsync();

            //获取物料信息
            PB_Material mdata = await Db.GetIQueryable<PB_Material>().Where(x => x.Id == data.MaterialId).FirstOrDefaultAsync();

            //获取派工单明细信息
            Expression<Func<PB_DispatchDetail, PB_ProductionOrder, RawMaterialPickingDTO>> select = (a, b) => new RawMaterialPickingDTO
            {
                DesignId = b.DesignId
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<PB_DispatchDetail>().AsExpandable()
                    join b in Db.GetIQueryable<PB_ProductionOrder>() on a.OrderId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    select @select.Invoke(a, b);
            var RMPdata = await q.Where(x => x.Id == DispatchDetailId).FirstOrDefaultAsync();

            string sqlMain = string.Empty;

            double? dnums = data.Nums;

            //库存减去领取物料信息
            List<IT_LocalDetail> LocalDetail = await Db.GetIQueryable<IT_LocalDetail>().Where(x => x.LocalId == LocalId && x.MaterialId == data.MaterialId).ToListAsync();
            foreach (var item in LocalDetail)
            {
                if (dnums > 0)
                {
                    //出库数量等于库存明细数量时
                    if (item.Num == dnums)
                    {
                        dnums -= item.Num;
                        sqlMain += @" update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)-" + item.Num + ",ScanNum=IFNULL(ScanNum,0)-" + item.Num + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                        sqlMain += @" delete from IT_LocalDetail where Id='" + item.Id + "'; ";
                    }
                    else if (item.Num < dnums)  //出库数量大于库存明细数量时
                    {
                        if (dnums >= item.Num)
                        {
                            dnums -= item.Num;
                            sqlMain += @" update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)-" + item.Num + ",ScanNum=IFNULL(ScanNum,0)-" + item.Num + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                            sqlMain += @" delete from IT_LocalDetail where Id='" + item.Id + "'; ";
                        }
                        else
                        {
                            dnums -= dnums;
                            sqlMain += @" update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)-" + dnums + ",ScanNum=IFNULL(ScanNum,0)-" + dnums + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                            sqlMain += @" update IT_LocalDetail set Num=IFNULL(Num,0)-" + dnums + " where Id='" + item.Id + "'; ";
                        }
                    }
                    else if (item.Num > dnums)//出库数量小于库存明细数量时
                    {
                        sqlMain += @" update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)-" + dnums + ",ScanNum=IFNULL(ScanNum,0)-" + dnums + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                        sqlMain += @" update IT_LocalDetail set Num=IFNULL(Num,0)-" + dnums + " where Id='" + item.Id + "'; ";
                        dnums -= dnums;
                    }
                }
            }
            List<PB_Stock> PB_Stocklist = await Db.GetIQueryable<PB_Stock>().Where(x => x.MaterialId == data.MaterialId && x.PackId == "").ToListAsync();
            dnums = data.Nums;
            if (PB_Stocklist.Count > 0)
            {
                foreach (var item in PB_Stocklist)
                {
                    if (dnums != 0)
                    {
                        //出库数量等于库存明细数量时
                        if (item.StockNumber == dnums)
                        {
                            dnums -= item.StockNumber;
                            sqlMain += @" delete from PB_Stock where Id='" + item.Id + "'; ";
                        }
                        else if (item.StockNumber < dnums)   //出库数量大于库存明细数量时
                        {
                            if (dnums >= item.StockNumber)
                            {
                                dnums -= item.StockNumber;
                                sqlMain += @" delete from PB_Stock where Id='" + item.Id + "'; ";
                            }
                            else
                            {
                                dnums -= dnums;
                                sqlMain += @" update PB_Stock set StockNumber=IFNULL(StockNumber,0)-" + dnums + " where Id='" + item.Id + "'; ";
                            }
                        }
                        else if (item.StockNumber > dnums) //出库数量小于库存明细数量时
                        {
                            item.StockNumber -= dnums;
                            sqlMain += @" update PB_Stock set StockNumber=IFNULL(StockNumber,0)-" + dnums + " where Id='" + item.Id + "'; ";
                            dnums -= dnums;
                        }
                    }
                    else
                    {
                        continue;
                    }
                }
            }
            await ExecuteSqlAsync(sqlMain);

            //保存原材料领料信息
            PB_RawMaterialReceive rawdata = new PB_RawMaterialReceive();
            string rawId = IdHelper.GetId();
            rawdata.Id = rawId;
            rawdata.DispatchId = RMPdata.ParentId;
            rawdata.DispatchDetailId = RMPdata.Id;
            rawdata.MaterialId = data.MaterialId;
            rawdata.OrderId = RMPdata.OrderId;
            rawdata.OrderDetailId = RMPdata.OrderDetailId;
            rawdata.ProjectId = RMPdata.ProjectId;
            rawdata.DesignId = RMPdata.DesignId;
            rawdata.Nums = data.Nums;
            rawdata.Weight = mdata.Weight;
            rawdata.CollectionTime = DateTime.Now;
            rawdata.CollectionUserId = _operator.UserId;
            rawdata.StorId = LocalMaterial.StorId;
            rawdata.LocalId = LocalId;
            rawdata.CreatorId = _operator.UserId;
            rawdata.CreateTime = DateTime.Now;
            rawdata.Deleted = false;
            rawdata.SubordinateProduct = RMPdata.SubordinateProduct;
            rawdata.ProcedureId = RMPdata.SubId;
            await Db.InsertAsync<PB_RawMaterialReceive>(rawdata);

            //增加出库日志
            PB_StockLog pblog = new PB_StockLog();
            pblog.Id = IdHelper.GetId();
            pblog.PackId = "";
            pblog.StorId = LocalMaterial.StorId;
            pblog.LocationId = LocalId;
            pblog.StockNumber = data.Nums;
            pblog.CreatorId = _operator.UserId;
            pblog.CreatorName = userdata.RealName;
            pblog.OperatorId = _operator.UserId;
            pblog.Type = 1;
            pblog.OrderID = rawId;
            pblog.CreateTime = DateTime.Now;
            pblog.State = 1;
            await Db.InsertAsync<PB_StockLog>(pblog);

            //出单Id
            string outId = IdHelper.GetId();

            //出库单明细汇总
            TD_OutStorRecord OutStorRecord = new TD_OutStorRecord();
            OutStorRecord.Id = IdHelper.GetId();
            OutStorRecord.StorId = LocalMaterial.StorId;
            OutStorRecord.OutStorId = outId;
            OutStorRecord.MaterialId = data.MaterialId;
            OutStorRecord.Price = mdata.Price;
            OutStorRecord.TotalAmt = mdata.Price * data.Nums;
            OutStorRecord.OutNum = Convert.ToDouble(data.Nums);
            OutStorRecord.CreatorId = _operator.UserId;
            OutStorRecord.CreateTime = DateTime.Now;
            OutStorRecord.Deleted = false;
            await Db.InsertAsync<TD_OutStorRecord>(OutStorRecord);
            //出库单明细
            TD_OutStorDetail OutStorDetail = new TD_OutStorDetail();
            OutStorDetail.Id = IdHelper.GetId();
            OutStorDetail.StorId = LocalMaterial.StorId;
            OutStorDetail.OutStorId = outId;
            OutStorDetail.LocalId = LocalId;
            OutStorDetail.MaterialId = data.MaterialId;
            OutStorDetail.Price = Convert.ToDouble(mdata.Price);
            OutStorDetail.TotalAmt = Convert.ToDouble(mdata.Price * data.Nums);
            OutStorDetail.OutNum = Convert.ToDouble(data.Nums);
            OutStorDetail.CreateTime = DateTime.Now;
            OutStorDetail.CreatorId = _operator.UserId;
            OutStorDetail.Deleted = false;
            OutStorDetail.OldOrNew = 1;
            await Db.InsertAsync<TD_OutStorDetail>(OutStorDetail);
            //出库单
            TD_OutStorage OutStorage = new TD_OutStorage();
            OutStorage.Id = outId;
            OutStorage.StorageId = LocalMaterial.StorId;
            OutStorage.Code = GetTimeStamp();
            OutStorage.OutTime = DateTime.Now;
            OutStorage.OutType = "ProductOut";  //生产领料
            OutStorage.RefCode = DispatchDetailId;
            OutStorage.OutNum = data.Nums;
            OutStorage.TotalAmt = data.Nums * mdata.Price;
            OutStorage.Status = 0;
            OutStorage.CreateTime = DateTime.Now;
            OutStorage.CreatorId = _operator.UserId;
            OutStorage.Deleted = false;
            OutStorage.QrCode = h_OutStorage + "," + outId;
            OutStorage.DepId = userdata.DepartmentId;
            OutStorage.ProjectId = RMPdata.ProjectId;
            OutStorage.DesignId = RMPdata.DesignId;
            OutStorage.FactoryId = userdata.FactoryId;
            await Db.InsertAsync<TD_OutStorage>(OutStorage);

        }
        /// <summary>
        /// PDA上件派工保存
        /// </summary>
        /// <param name="Data"></param>
        /// <returns></returns>
        public async Task <PieceDispatch> SavePieceDispatch(PieceDispatch Data, RedisOptions _redisOptions)
        {


            var base_user =await Db.GetIQueryable<Base_User>().Where(m=>m.Id==_operator.UserId).FirstOrDefaultAsync();

            string workorderNo = "";
            //实例化Redis操作类
            _redisOptions.DbChannel = (int)Redis_Type.redis6;
            RedisSetHelper redis = new RedisSetHelper(_redisOptions);
            if (RedisSetHelper.Exists("DispatchNumber"))
            {
                string thedate = DateTime.Now.ToString("yyyy-MM-dd");
                if (redis.GetString("DispatchTime") == thedate)
                {
                    redis.SetString("DispatchNumber", (Convert.ToInt32(redis.GetString("DispatchNumber")) + 1).ToString());
                    workorderNo = "D_" + DateTime.Now.ToString("yyyyMMdd") + "_" + redis.GetString("DispatchNumber").PadLeft(4, '0');
                }
                else
                {
                    redis.SetString("DispatchTime", thedate);
                    redis.SetString("DispatchNumber", "1");
                    workorderNo = "D_" + DateTime.Now.ToString("yyyyMMdd") + "_" + redis.GetString("DispatchNumber").PadLeft(4, '0');
                }
            }
            else
            {
                redis.SetString("DispatchNumber", "0");
                redis.SetString("DispatchNumber", (Convert.ToInt32(redis.GetString("DispatchNumber")) + 1).ToString());
                workorderNo = "D_" + DateTime.Now.ToString("yyyyMMdd") + "_" + redis.GetString("DispatchNumber").PadLeft(4, '0');
            }
            string Id = IdHelper.GetId();
            List<PB_DispatchDetail> subdata = new List<PB_DispatchDetail>();
            List<PB_DispatchGroup> pB_DispatchGroups = new List<PB_DispatchGroup>();
            foreach (var item in Data.SubData)
            {
                PB_DispatchDetail pB_DispatchDetail = new PB_DispatchDetail();
               
                String SubId = IdHelper.GetId();
                pB_DispatchDetail.Id = SubId;
                pB_DispatchDetail.ParentId = Id;
                pB_DispatchDetail.IsRefurbishment = 1;
                pB_DispatchDetail.SubId = item.SubId;//工序id
                pB_DispatchDetail.ProjectId = item.ProjectId;
                pB_DispatchDetail.MaterialId = item.MaterialId;//流转单物料id
                pB_DispatchDetail.OrderId = item.OrderId;//流转单任务单id
                pB_DispatchDetail.OrderDetailId = item.OrderDetailId;//流转单任务单明细id
                pB_DispatchDetail.Aperture = item.Aperture;
                pB_DispatchDetail.Texture = item.Texture;
                pB_DispatchDetail.Nums = item.Nums;//流转单接收数量
                pB_DispatchDetail.QrCode = "16," + SubId;
                pB_DispatchDetail.CreatorId = _operator.UserId;
                pB_DispatchDetail.CreatorRealName = base_user.RealName;
                pB_DispatchDetail.CreateTime = DateTime.Now;
                pB_DispatchDetail.Deleted = false;
                pB_DispatchDetail.WorkReportQuantity =0; //报工数量
                pB_DispatchDetail.ReadyNums = 0;//已经领取的数量
                pB_DispatchDetail.State = 0;
                pB_DispatchDetail.SubordinateProduct = item.SubordinateProduct;//流转单里的所属成品
                pB_DispatchDetail.MaterialLength = item.MaterialLength;
                pB_DispatchDetail.PunchingNums = item.PunchingNums;
                pB_DispatchDetail.PunchingPosition = item.PunchingPosition;
                subdata.Add(pB_DispatchDetail);

                //派工与流转卡对应关系表
                PB_DispatchGroup pB_DispatchGroup = new PB_DispatchGroup();
                pB_DispatchGroup.Id = IdHelper.GetId();
                pB_DispatchGroup.DispatchId = Id;
                pB_DispatchGroup.CirBillId = item.CirBillId;
                pB_DispatchGroup.TaskId = item.OrderId;
                pB_DispatchGroup.TaskDetailId = item.OrderDetailId;
                pB_DispatchGroup.DispatchDetailId = SubId;
                pB_DispatchGroups.Add(pB_DispatchGroup);
            }

            string StaffId = "";
            //批量插入员工id
            for (int i=0;i<Data.StaffIdList.Count();i++) {
                if (i==0) {
                    StaffId +=  Data.StaffIdList[i];
                }
                else{
                    StaffId += "," + Data.StaffIdList[i];
                }
               
             }
            string StaffName = "";
            for (int k=0;k<Data.StaffNameList.Count();k++) {
                if (k== 0)
                {
                    StaffName += Data.StaffNameList[k];
                }
                else
                {
                    StaffName += "," + Data.StaffNameList[k];
                }
            }
           
            PB_Dispatch pB_Dispatch = new PB_Dispatch();
            pB_Dispatch.Id = Id;
            pB_Dispatch.StaffId = StaffId;
            pB_Dispatch.StaffName = StaffName;
            pB_Dispatch.WorkOrderNo = workorderNo;
            pB_Dispatch.DeptId = _operator.Property.DepartmentId;
            pB_Dispatch.TeamType = Data.TeamType;
            pB_Dispatch.DispatchDate = DateTime.Now;
            pB_Dispatch.CreatorId = _operator.UserId;
            pB_Dispatch.CreatorRealName = base_user.RealName;
            pB_Dispatch.CreateTime = DateTime.Now;
            pB_Dispatch.Deleted = false;
            pB_Dispatch.QrCode = "33," + Id;
            pB_Dispatch.State = 0;
            
            await Db.InsertAsync<PB_DispatchDetail>(subdata);
            await Db.InsertAsync<PB_DispatchGroup>(pB_DispatchGroups);
            await Db.InsertAsync<PB_Dispatch>(pB_Dispatch);
           
            Expression<Func<PB_Dispatch, Base_Department, PB_ShiftType, PB_DispatchDTO>> selects = (a, b, c) => new PB_DispatchDTO
            {
                DepartmentName = b.Name,
                TeamTypeName = c.Name
            };
            selects = selects.BuildExtendSelectExpre();
            var ks = from a in GetIQueryable().AsExpandable()
                    join b in Db.GetIQueryable<Base_Department>() on a.DeptId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<PB_ShiftType>() on a.TeamType equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    select @selects.Invoke(a, b, c);

            PB_DispatchDTO listdata = new PB_DispatchDTO();
            var qdata = await ks.Where(x =>x.Id==Id).FirstOrDefaultAsync();
          
                Expression<Func<PB_DispatchDetail, PB_Material, PB_Project, PB_ProductionOrder, PB_ProcecureSub, PB_DesignList, PB_Buildingnumber, PB_Material, PB_DispatchDetailDTO>> select = (a, b, c, d, e, f, g, h) => new PB_DispatchDetailDTO
                {
                    MaterialIdName = b.Name,
                    MateralldCode = b.QrCode,
                    ProName = c.Name,
                    ProductionOrderName = d.POName,
                    BatchNumber = d.BatchNumber,
                    Code = d.Code,
                    SubName = e.Name,
                    MaterSpec = b.Spec,
                    DanZhong = b.Weight==null?0:b.Weight,
                    Zongzhong = a.Nums * b.Weight==null?0:a.Nums*b.Weight,
                    ProcessingRequirements = (a.Aperture + a.Texture)==null?"": (a.Aperture + a.Texture),
                    BuildNo = g.Buildingnumber,
                    SubordinateProductName = h.Name==null?"":h.Name

                };
                select = select.BuildExtendSelectExpre();
                var q = from a in Db.GetIQueryable<PB_DispatchDetail>().AsExpandable()
                        join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
                        from b in ab.DefaultIfEmpty()
                        join c in Db.GetIQueryable<PB_Project>() on a.ProjectId equals c.Id into ac
                        from c in ac.DefaultIfEmpty()
                        join d in Db.GetIQueryable<PB_ProductionOrder>() on a.OrderId equals d.Id into ad
                        from d in ad.DefaultIfEmpty()
                        join e in Db.GetIQueryable<PB_ProcecureSub>() on a.SubId equals e.Id into ae
                        from e in ae.DefaultIfEmpty()
                        join f in Db.GetIQueryable<PB_DesignList>() on d.DesignId equals f.Id into df
                        from f in df.DefaultIfEmpty()
                        join g in Db.GetIQueryable<PB_Buildingnumber>() on f.BuildingNumberID equals g.Id into fg
                        from g in fg.DefaultIfEmpty()
                        join h in Db.GetIQueryable<PB_Material>() on a.SubordinateProduct equals h.Id into ah
                        from h in ah.DefaultIfEmpty()
                        select @select.Invoke(a, b, c, d, e, f, g, h);
                q = q.Where(x => x.ParentId == Id);
                var res = await q.ToListAsync();
                PB_DispatchDTO pblist = new PB_DispatchDTO();
                pblist.Id = qdata.Id;
                pblist.DepartmentName = qdata.DepartmentName;
                pblist.QrCode = qdata.QrCode;
                pblist.TeamTypeName = qdata.TeamTypeName;
                pblist.StaffId = qdata.StaffId;
                pblist.StaffName = qdata.StaffName;
                pblist.WorkOrderNo = qdata.WorkOrderNo;
                pblist.DeptId = qdata.DeptId;
                pblist.TeamType = qdata.TeamType;
                pblist.DispatchDate = qdata.DispatchDate;
                pblist.CreatorId = qdata.CreatorId;
                pblist.CreatorRealName = qdata.CreatorRealName;
                pblist.CreateTime = qdata.CreateTime;
                pblist.Deleted = qdata.Deleted;
                pblist.subdata = res;


            PieceDispatch axjxdis = new PieceDispatch
            {
                    Success = true,
                    Msg = "请求成功",
                    Data = pblist
                };

            return axjxdis;
        }

        public async Task<List<PB_CirculationBillInfo>> GetCirculationBillInf(string QrCode) {
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("SELECT    ");
            sql.AppendLine("PB_CirculationBill.*, ");
            sql.AppendLine("PB_Material.`Name`, ");
            sql.AppendLine("PB_Material.Spec, ");
            sql.AppendLine("PB_Material.Code ");
            sql.AppendLine("FROM ");
            sql.AppendLine("`PB_CirculationBill` ");
            sql.AppendLine("LEFT JOIN PB_Material ON PB_CirculationBill.MaterialId = PB_Material.Id ");
            sql.AppendLine("WHERE ");
            sql.AppendLine("PB_CirculationBill.Id = '"+QrCode.Split(',')[1].ToString()+"' ");
            sql.AppendLine("and StatisticalQuantity>=0");
            var res =await Db.GetListBySqlAsync<PB_CirculationBillInfo>(sql.ToString());
            if (res.Count == 0)
            {
                throw new BusException("该流转单未统计");
            }
            else {
                var count =await Db.GetIQueryable<PB_DispatchGroup>().Where(m=>m.CirBillId==QrCode.Split(',')[1].ToString()).FirstOrDefaultAsync();
                if (count!=null) {
                    throw new BusException("该流转单已派工!");
                }
            }
            return res;
        }
        /// <summary>
        /// 上件派工扫描流转单获取接收数量
        /// </summary>
        /// <param name="QrCode"></param>
        /// <returns></returns>
        public async Task<List<PartialDispatch>> GetPartialDispatchList(string QrCode)
        {

            
            //获取流转单号
            string CirBillId = QrCode.Split(',')[1].ToString();
            List<PartialDispatch> ListData = new List<PartialDispatch>();
            var db =await Db.GetIQueryable<PB_CirculationBill>().Where(m => m.Id == CirBillId).FirstOrDefaultAsync();
            if (db==null) {
                throw new BusException("流转单号不正确!");
            }
            //验证是否统计
            if (db.StatisticalQuantity==0) {
                throw new BusException("该流转单未统计!");
            }
            //验证接收人是否为空
            if (db.Receiver == null && db.StatisticalQuantity > 0) {
                string sqlupdate = "update PB_CirculationBill set ReceivingQuantity='" + db.StatisticalQuantity + "',Receiver='" + _operator.UserId + "',ReceivingTime='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' where Id='" + CirBillId + "'";
                await Db.ExecuteSqlAsync(sqlupdate);
            }
            StringBuilder sql = new StringBuilder();
            sql.AppendLine(" SELECT  ");
            sql.AppendLine(" A.Id Id,  ");
            sql.AppendLine("A.ReceivingQuantity,");
            sql.AppendLine("A.StatisticalQuantity,  ");
            sql.AppendLine("A.MaterialId,  ");
            sql.AppendLine("A.SubordinateProduct,  ");
            sql.AppendLine(" A.WorkReportQuantity,  ");
            sql.AppendLine(" A.Reamrk,  ");
            sql.AppendLine("A.TaskId,  ");
            sql.AppendLine("A.TaskDetailId,  ");
            sql.AppendLine("C.`Code` MaterCode,  ");
            sql.AppendLine("C.`Name` MaterName,  ");
            sql.AppendLine("C.Spec MaterSpec,  ");
            sql.AppendLine("IFNULL(A.Receiver,'') Receiver,");
            sql.AppendLine("IFNULL( SUM(B.Nums), 0 ) DispatchedNums  ");
            sql.AppendLine(" FROM  ");
            sql.AppendLine("PB_CirculationBill AS A  ");
            sql.AppendLine("LEFT JOIN PB_DispatchGroup AS D ON A.Id = D.CirBillId  ");
            sql.AppendLine("LEFT JOIN PB_DispatchDetail AS B ON B.Id = D.DispatchDetailId  ");
            sql.AppendLine("LEFT JOIN PB_Material AS C ON A.MaterialId = C.Id  ");
            sql.AppendLine("WHERE  ");
            sql.AppendLine("A.Id = '"+CirBillId+"'  ");
            sql.AppendLine("AND A.StatisticalQuantity > 0  ");
            sql.AppendLine("GROUP BY  ");
            sql.AppendLine("A.Id,  ");
            sql.AppendLine(" B.`MaterialId`,  ");
            sql.AppendLine(" D.TaskId,  ");
            sql.AppendLine("D.TaskDetailId  ");
            sql.AppendLine(" HAVING  A.ReceivingQuantity>DispatchedNums");
            ListData = await Db.GetListBySqlAsync<PartialDispatch>(sql.ToString());
            if (ListData.Count==0) {
                throw new BusException("该流转单已全部派工!");
            }
            
            return ListData;
        }


        #endregion

        #region 流转卡初始化报工数据,反写生产任务单已生产数量

        /// <summary>
        /// 获取流转单信息
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageResultData<CirculationOrder>> GetCirculationOrderList(PageInput<CirculationOrderConditionDTO> input)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.AppendLine(" SELECT");

            stringBuilder.AppendLine(" a.Id,");

            stringBuilder.AppendLine(" a.QrCode,");

            stringBuilder.AppendLine("  a.MaterialId,");

            stringBuilder.AppendLine(" b.`Code`,");

            stringBuilder.AppendLine(" b.`Name`,");

            stringBuilder.AppendLine(" IFNULL(b.Spec, '') Spec,");

            stringBuilder.AppendLine(" a.SendTime,");

            stringBuilder.AppendLine(" ifnull(a.WorkReportQuantity,0) WorkReportQuantity,");

            stringBuilder.AppendLine(" d.Id DispatchId,");
            stringBuilder.AppendLine("o.POName,");

            stringBuilder.AppendLine(" users.RealName,");

            stringBuilder.AppendLine("ShiftType.`Name` ShiftTypeName,");

            stringBuilder.AppendLine(" Sub.`Name` SubName,");

            stringBuilder.AppendLine(" ProMater.`Name` ProMaterName,");

            stringBuilder.AppendLine(" IFNULL(b.Weight, 0)  Weight,");

            stringBuilder.AppendLine(" IFNULL(a.WorkReportQuantity * b.Weight, 0) SumWeight,");

            stringBuilder.AppendLine(" (select IFNULL(SUM(Nums),0) as JobNums from PB_JobBooking where CirBillId=a.Id) as JobNums");

            stringBuilder.AppendLine(" FROM");

            stringBuilder.AppendLine("`PB_CirculationBill` AS a");

            stringBuilder.AppendLine(" LEFT JOIN PB_Material AS b ON a.MaterialId = b.Id");

            stringBuilder.AppendLine(" LEFT JOIN PB_ProductionOrder AS o on a.TaskId = o.Id");

            stringBuilder.AppendLine(" LEFT JOIN PB_Dispatch as d on a.DispatchId = d.Id");

            stringBuilder.AppendLine("  LEFT JOIN Base_User AS users on d.StaffId = users.Id");

            stringBuilder.AppendLine("  LEFT JOIN PB_ShiftType AS ShiftType ON d.TeamType = ShiftType.Id");

            stringBuilder.AppendLine(" LEFT JOIN PB_ProcecureSub AS Sub on a.ProcedureId = Sub.Id");

            stringBuilder.AppendLine(" LEFT JOIN PB_Material AS ProMater on a.SubordinateProduct = ProMater.Id");
            stringBuilder.AppendLine(" where 1=1 ");
            if (!input.Search.OrderTimeStart.IsNullOrEmpty() && !input.Search.OrderTimeEnd.IsNullOrEmpty())
            {
                stringBuilder.AppendLine(" and a.SendTime between   '" + Convert.ToDateTime(input.Search.OrderTimeStart).ToString("yyyy-MM-dd 00:00:00") + "'   and '" + Convert.ToDateTime(input.Search.OrderTimeEnd).ToString("yyyy-MM-dd 23:59:59") + "'");
            }
            if (!input.Search.SubId.IsNullOrEmpty())
            {
                stringBuilder.AppendLine(" and a.ProcedureId='"+ input.Search.SubId +"' ");
            }
            if (!input.Search.Keyword.IsNullOrEmpty())
            {
                stringBuilder.AppendLine("  and ProMater.`Name` like '%"+ input.Search.Keyword +"%'");
            }

            stringBuilder.AppendLine($" ORDER BY a.SendTime {input.SortType}");

            List<CirculationOrder> res = await Db.GetListBySqlAsync<CirculationOrder>(stringBuilder.ToString());
            #region   分页处理数据   有bug 没有按照主数据进行处理
            int pagecount;//总页数
            List<CirculationOrder> listpro_down = res;//一个要分页的，类型数据集合，存储的是所有的数据
            List<CirculationOrder> productes = new List<CirculationOrder>();//用来存储要展示的数据
            int pagees = listpro_down.Count;//所有数据的总条数
                                            //计算总页数
            if (pagees % input.PageRows == 0)
            {
                pagecount = pagees / input.PageRows;
            }
            else
            {
                pagecount = (pagees / input.PageRows) + 1;
            }
            productes = listpro_down.Skip((input.PageIndex - 1) * input.PageRows).Take(input.PageRows).ToList();//分页的重点

            return new PageResultData<CirculationOrder> { Total = pagees, Data = productes };
            #endregion
        }

        /// <summary>
        /// 获取人员信息
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageResult<JobUserDTO>> GetJobUserListAsync(PageInput<ConditionDTO> input)
        {
            var where = LinqHelper.True<JobUserDTO>();
            var search = input.Search;
            Expression<Func<Base_User, Base_Department, JobUserDTO>> select = (a, b) => new JobUserDTO
            {
                Id = a.Id,
                RealName = a.RealName,
                DepartmentId = a.DepartmentId,
                DepartmentName = b.Name
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<Base_User>().AsExpandable()
                    join b in Db.GetIQueryable<Base_Department>() on a.DepartmentId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    select @select.Invoke(a, b);
            if (!search.Keyword.IsNullOrEmpty())
            {
                q = q.Where(x => x.RealName.Contains(search.Keyword));
            }
            return await q.Where(where).GetPageResultAsync(input);
        }

        /// <summary>
        /// 保存(反向生成上件派工数据,生成报工数据,反写生产任务单已生产数量)
        /// </summary>
        /// <param name="data"></param>
        /// <param name="_redisOptions"></param>
        /// <returns></returns>
        [Transactional]
        public async Task SaveCirBillJobDataAsync(CirBillJobData data, RedisOptions _redisOptions)
        {
            Base_User userdata = await Db.GetIQueryable<Base_User>().Where(x => x.Id == _operator.UserId).FirstOrDefaultAsync();
            //获取流转单Id
            List<string> cirbillIds = new List<string>();
            foreach (var item in data.CirBillData)
            {
                cirbillIds.Add(item.CirBillId);
            }

            PB_ProcecureSub subdata = await Db.GetIQueryable<PB_ProcecureSub>().Where(x => x.Name == "上件").FirstOrDefaultAsync();
            data.SubId = subdata.Id;

            //获取流转信息
            Expression<Func<PB_CirculationBill, PB_ProductionOrder, PB_DesignList, CirculationBillJobDTO>> select = (a, b, c) => new CirculationBillJobDTO
            {
                ProjectId = c.ProjectId
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<PB_CirculationBill>().AsExpandable()
                    join b in Db.GetIQueryable<PB_ProductionOrder>() on a.TaskId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<PB_DesignList>() on b.DesignId equals c.Id into bc
                    from c in bc.DefaultIfEmpty()
                    select @select.Invoke(a, b, c);

            List<CirculationBillJobDTO> billlist = await q.Where(x => cirbillIds.Contains(x.Id)).ToListAsync();

            //根据工序获取设备信息
            Base_Device Device = await Db.GetIQueryable<Base_Device>().Where(x => x.ProcecureSubId == data.SubId).FirstOrDefaultAsync();

            //获取物料信息
            List<PB_Material> Material = await Db.GetIQueryable<PB_Material>().ToListAsync();

            //派工主表
            List<PB_Dispatch> Dispatch = new List<PB_Dispatch>();
            //派工明细
            List<PB_DispatchDetail> DispatchDetail = new List<PB_DispatchDetail>();
            //派工单与流转卡关系对应表
            List<PB_DispatchGroup> DispatchGroup = new List<PB_DispatchGroup>();
            //报工表
            List<PB_JobBooking> JobBooking = new List<PB_JobBooking>();

            //派工Id
            string dispathId = IdHelper.GetId();

            foreach (var item in billlist)
            {
                var itemdata = data.CirBillData.Where(x => x.CirBillId == item.Id).FirstOrDefault();
                string DispatchDetailId = IdHelper.GetId();
                //派工明细
                PB_DispatchDetail dd = new PB_DispatchDetail();
                dd.Id = DispatchDetailId;
                dd.ParentId = dispathId;
                dd.IsRefurbishment = item.Type;
                dd.SubId = data.SubId;
                dd.ProjectId = item.ProjectId;
                dd.MaterialId = item.SubordinateProduct;
                dd.OrderId = item.TaskId;
                dd.OrderDetailId = item.TaskDetailId;
                dd.Nums = itemdata.Nums;
                dd.QrCode = h_DispatchDetail + "," + DispatchDetailId;
                dd.CreatorId = _operator.UserId;
                dd.CreatorRealName = userdata.RealName;
                dd.CreateTime = DateTime.Now;
                dd.Deleted = false;
                dd.WorkReportQuantity = itemdata.Nums;
                dd.ReadyNums = 0;
                dd.SubordinateProduct = item.SubordinateProduct;
                DispatchDetail.Add(dd);

                //派工单与流转卡关系对应表
                PB_DispatchGroup dg = new PB_DispatchGroup();
                dg.Id = IdHelper.GetId();
                dg.DispatchId = dispathId;
                dg.CirBillId = item.Id;
                dg.TaskId = item.TaskId;
                dg.TaskDetailId = item.TaskDetailId;
                dg.DispatchDetailId = DispatchDetailId;
                DispatchGroup.Add(dg);
            }

            //派工表
            PB_Dispatch dis = new PB_Dispatch();
            dis.Id = dispathId;
            dis.StaffId = string.Join(",", data.StaffId.ToArray());
            dis.StaffName = string.Join(",", data.StaffName.ToArray());
            #region  获取工单号
            //实例化Redis操作类
            string workorderNo = "";
            _redisOptions.DbChannel = (int)Redis_Type.redis6;
            RedisSetHelper redis = new RedisSetHelper(_redisOptions);
            if (RedisSetHelper.Exists("DispatchNumber"))
            {
                string thedate = DateTime.Now.ToString("yyyy-MM-dd");
                if (redis.GetString("DispatchTime") == thedate)
                {
                    redis.SetString("DispatchNumber", (Convert.ToInt32(redis.GetString("DispatchNumber")) + 1).ToString());
                    workorderNo = "D_" + DateTime.Now.ToString("yyyyMMdd") + "_" + redis.GetString("DispatchNumber").PadLeft(4, '0');
                }
                else
                {
                    redis.SetString("DispatchTime", thedate);
                    redis.SetString("DispatchNumber", "1");
                    workorderNo = "D_" + DateTime.Now.ToString("yyyyMMdd") + "_" + redis.GetString("DispatchNumber").PadLeft(4, '0');
                }
            }
            else
            {
                redis.SetString("DispatchNumber", "0");
                redis.SetString("DispatchNumber", (Convert.ToInt32(redis.GetString("DispatchNumber")) + 1).ToString());
                workorderNo = "D_" + DateTime.Now.ToString("yyyyMMdd") + "_" + redis.GetString("DispatchNumber").PadLeft(4, '0');
            }
            #endregion
            dis.WorkOrderNo = workorderNo;
            dis.DeptId = data.WorkShopId;
            dis.DispatchDate = Convert.ToDateTime(data.JobDate);
            dis.CreatorId = _operator.UserId;
            dis.CreatorRealName = userdata.RealName;
            dis.CreateTime = DateTime.Now;
            dis.Deleted = false;
            dis.QrCode = h_Dispatch + "," + dispathId;
            dis.State = 0;
            Dispatch.Add(dis);

            //去派工人员的第一个人
            string ReportUserId = string.Join(",", data.StaffId.ToArray()).Split(',')?[0].ToString();
            //获取报工人信息
            Base_User report = await Db.GetIQueryable<Base_User>().Where(x => x.Id == ReportUserId).FirstOrDefaultAsync();
            //质检人信息
            Base_User Quality = await Db.GetIQueryable<Base_User>().Where(x => x.Id == data.QualityUserId).FirstOrDefaultAsync();
            //统计人信息
            Base_User Statistics = await Db.GetIQueryable<Base_User>().Where(x => x.Id == data.StatisticsUserId).FirstOrDefaultAsync();

            string sqlMain = string.Empty;
            foreach (var item in DispatchGroup)
            {
                var itemdata = DispatchDetail.Where(x => x.Id == item.DispatchDetailId).FirstOrDefault();
                //报工
                PB_JobBooking jobdata = new PB_JobBooking();
                jobdata.Id = IdHelper.GetId();
                jobdata.ParentId = item.TaskDetailId;
                jobdata.Nums = itemdata.Nums;
                jobdata.ReturnNums = itemdata.Nums;
                jobdata.DeviceId = Device.Id;
                jobdata.DeptId = userdata.FactoryId;
                jobdata.ReportUserId = ReportUserId;
                jobdata.ReportUserName = report.RealName;
                jobdata.ReportTime = Convert.ToDateTime(data.JobDate);
                jobdata.QualityUserId = data.QualityUserId;
                jobdata.QualityUserName = Quality.RealName;
                jobdata.QualityTime = Convert.ToDateTime(data.JobDate);
                jobdata.StatisticsUserId = data.StatisticsUserId;
                jobdata.StatisticsUserName = Statistics.RealName;
                jobdata.StatisticsNums = itemdata.Nums;
                jobdata.TakeOverUserId = data.StatisticsUserId;
                jobdata.TakeOverUserName = Statistics.RealName;
                jobdata.TakeOverTime= Convert.ToDateTime(data.JobDate);
                jobdata.CreateTime= Convert.ToDateTime(data.JobDate);
                jobdata.CreatorId = _operator.UserId;
                jobdata.CreatorRealName = userdata.RealName;
                jobdata.Status = 2;
                jobdata.Deleted = false;
                jobdata.MaterialId = itemdata.MaterialId;
                jobdata.MaterialName = Material.Where(x => x.Id == itemdata.MaterialId).FirstOrDefault().Name;
                jobdata.WorkshopId = data.WorkShopId;
                jobdata.OldOrNew = itemdata.IsRefurbishment;
                jobdata.JobType = 1;
                jobdata.CirBillId = item.CirBillId;
                jobdata.DispatchDetailId = item.DispatchDetailId;
                jobdata.Remarks = "7月份数据生成上件报工的派工、报工数据,反写生产任务单已生产数量";
                JobBooking.Add(jobdata);

                sqlMain += @" update PB_ProductionOrderDetail set CompleteNums=IFNULL(CompleteNums,0)+" + itemdata.Nums + " where Id='" + item.TaskDetailId + "'; ";

            }

            await Db.InsertAsync<PB_Dispatch>(Dispatch);
            await Db.InsertAsync<PB_DispatchDetail>(DispatchDetail);
            await Db.InsertAsync<PB_DispatchGroup>(DispatchGroup);
            await Db.InsertAsync<PB_JobBooking>(JobBooking);

            await Db.ExecuteSqlAsync(sqlMain);

        }

        #endregion

        #region 组装件领料

        /// <summary>
        /// 组装件获取包信息
        /// </summary>
        /// <param name="PackQrCode">包二维码</param>
        /// <param name="DispatchDetailQrCode">派工单明细二维码</param>
        /// <returns></returns>
        public async Task<BomPackDTO> GetBomPackDataAsync(string PackQrCode, string DispatchDetailQrCode)
        {
            string packid = PackQrCode.Split(',')?[1].ToString();

            string dispatchdetailid = DispatchDetailQrCode.Split(',')?[1].ToString();

            PB_PackList packdata = await Db.GetIQueryable<PB_PackList>().Where(x => x.Id == packid).FirstOrDefaultAsync();
            if (packdata != null)
            {
                string sqlMain = @"select a.PackId,a.MaterialId,b.`Name` as MaterialName,b.`Code` as MaterialCode,b.Spec as MaterialSpec,b.ProductCode,a.Nums from PB_PackMaterialCount as a left join PB_Material as b on a.MaterialId=b.Id where a.PackId='" + packid + "'";
                List<BomPackMaterialDTO> BomPackMaterial = await Db.GetListBySqlAsync<BomPackMaterialDTO>(sqlMain);

                List<string> materialids = new List<string>();
                foreach (var item in BomPackMaterial)
                {
                    materialids.Add(item.MaterialId);
                }

                //获取派工单组装需要的物料信息
                sqlMain = @"select c.MaterailId as MaterialId,(a.Nums*c.Quantity) as Nums from PB_DispatchDetail as a
left join Bom_MainNode as b on a.MaterialId=b.MaterailId
left join Bom_SubNode as c on b.Id=c.ParentId
where a.Id='" + dispatchdetailid + "' and c.MaterailId in ('" + string.Join("','", materialids.ToArray()) + "')";
                List<BomSubMaterialDTO> BomSubMaterial = await Db.GetListBySqlAsync<BomSubMaterialDTO>(sqlMain);

                //已领取物料信息
                List<PB_BomMaterialReceive> BomMaterialReceive = await Db.GetIQueryable<PB_BomMaterialReceive>().Where(x => x.DispatchDetailId == dispatchdetailid).ToListAsync();
                var MaterialReceive = BomMaterialReceive.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, Nums = x.Sum(i => i.Nums) }).ToList();

                foreach (var item in BomPackMaterial)
                {
                    //获取组装需要的物料信息
                    var itemdata = BomSubMaterial.Where(x => x.MaterialId == item.MaterialId).FirstOrDefault();
                    //获取已领取的物料信息
                    var itemreceive = MaterialReceive.Where(x => x.MaterialId == item.MaterialId).FirstOrDefault();

                    item.RemainingQuantity = (itemdata.Nums - (itemreceive == null ? 0 : itemreceive.Nums));
                }

                BomPackDTO supack = new BomPackDTO();
                supack.PackId = packdata.Id;
                supack.PackQrCode = packdata.QrCode;
                supack.BoxName = packdata.BoxName;
                supack.Nums = packdata.Nums;
                supack.BomPackMaterial = BomPackMaterial;
                return supack;
            }
            else
            {
                throw new BusException("请扫描正确的包信息");
            }
        }

        /// <summary>
        /// 组装件获取单个物料信息
        /// </summary>
        /// <param name="PackQrCode">物料二维码</param>
        /// <param name="DispatchDetailQrCode">派工单明细二维码</param>
        /// <returns></returns>
        public async Task<BomSingleMaterialDTO> GetBomSingleMaterialDataAsync(string PackQrCode, string DispatchDetailQrCode)
        {
            var packmodel = PackQrCode.Split(',')?[0];
            string materialId = string.Empty;
            if (packmodel == "04")
            {
                var model = PackQrCode.Split(',');
                if (model.Length > 2)
                {
                    if (model.Length < 4)
                    {
                        throw new BusException("请扫描或输入正确的物料二维码");
                    }
                    else
                    {
                        materialId = model?[3].ToString();
                    }
                }
                else
                {
                    materialId = model?[1].ToString();
                }
            }
            string dispatchdetailid = DispatchDetailQrCode.Split(',')?[1].ToString();

            //合并重复物料指向新的物料Id
            PB_Material materdata = await Db.GetIQueryable<PB_Material>().Where(x => x.Id == materialId).FirstOrDefaultAsync();
            if (!materdata.TransferId.IsNullOrEmpty())
            {
                materialId = materdata.TransferId;
            }
            Expression<Func<PB_DispatchDetail, Bom_MainNode, Bom_SubNode, PB_Material, BomSingleMaterialDTO>> select = (a, b, c, d) => new BomSingleMaterialDTO
            {
                DispatchDetailId = a.Id,
                MaterialId = c.MaterailId,
                MaterialName = c.MaterialName,
                MaterialCode = d.Code,
                MaterialSpec = d.Spec,
                ProductCode = d.ProductCode,
                Nums = a.Nums * c.Quantity
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<PB_DispatchDetail>().AsExpandable()
                    join b in Db.GetIQueryable<Bom_MainNode>() on a.MaterialId equals b.MaterailId into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<Bom_SubNode>() on b.Id equals c.ParentId into bc
                    from c in bc.DefaultIfEmpty()
                    join d in Db.GetIQueryable<PB_Material>() on c.MaterailId equals d.Id into cd
                    from d in cd.DefaultIfEmpty()
                    select @select.Invoke(a, b, c, d);
            var res = await q.Where(x => x.DispatchDetailId == dispatchdetailid && x.MaterialId == materialId).FirstOrDefaultAsync();
            List<PB_BomMaterialReceive> BomMaterialReceive = await Db.GetIQueryable<PB_BomMaterialReceive>().Where(x => x.DispatchDetailId == dispatchdetailid && x.MaterialId == materialId).ToListAsync();
            var BomMaterial = BomMaterialReceive.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, Nums = x.Sum(i => i.Nums) }).FirstOrDefault();
            if (res == null)
            {
                throw new BusException("请检查物料与派工单组装物料信息是否匹配");
            }
            res.RemainingQuantity = (res.Nums - (BomMaterial == null ? 0 : BomMaterial.Nums));
            res.Nums = (res.Nums - (BomMaterial == null ? 0 : BomMaterial.Nums));
            return res;
        }

        /// <summary>
        /// 获取派工单领取明细
        /// </summary>
        /// <param name="DispatchDetailQrCode">派工单明细二维码</param>
        /// <returns></returns>
        public async Task<List<BomMaterialReceiveMaterialDTO>> GetBomMaterialReceiveListAsync(string DispatchDetailQrCode)
        {
            string dispatchdetailid = DispatchDetailQrCode.Split(',')?[1].ToString();
            string sql = @"select * from (
select a.Id,a.PackId,a.MaterialId,a.JobId,a.CreateTime,b.`Code` as MaterialCode,b.`Name` as BoxName,b.Spec as MaterialSpec,b.ProductCode,a.Nums,a.LocalId,c.StorAreaName from PB_BomMaterialReceive as a
left join PB_Material as b on a.MaterialId=b.Id
left join View_StorAreaRackLocation as c on a.LocalId=c.Id
where a.DispatchDetailId='{0}' and a.PackId='' and a.JobId='' and a.Status=0
UNION ALL
select a.Id,a.PackId,a.MaterialId,a.JobId,a.CreateTime,b.`Code` as MaterialCode,b.`Name` as BoxName,b.Spec as MaterialSpec,b.ProductCode,a.Nums,a.LocalId,c.StorAreaName from PB_BomMaterialReceive as a
left join PB_Material as b on a.MaterialId=b.Id
left join View_StorAreaRackLocation as c on a.LocalId=c.Id
where a.DispatchDetailId='{0}' and a.PackId='' and a.JobId!='' and a.Status=0
UNION ALL
select a.Id,a.PackId,a.MaterialId,a.JobId,a.CreateTime,b.`Code` as MaterialCode,d.BoxName,b.Spec as MaterialSpec,b.ProductCode,a.Nums,a.LocalId,c.StorAreaName from PB_BomMaterialReceive as a
left join PB_Material as b on a.MaterialId=b.Id
left join View_StorAreaRackLocation as c on a.LocalId=c.Id
left join PB_PackList as d on a.PackId=d.Id
where a.DispatchDetailId='{0}' and a.PackId!='' and d.PackageType=0 and a.Status=0
UNION ALL
select '' as Id,a.PackId,'' as MaterialId,'' JobId,DATE_FORMAT(a.CreateTime,'%Y-%m-%d') as CreateTime,'' as MaterialCode,b.BoxName,'' as MaterialSpec,'' as ProductCode,SUM(a.Nums) as Nums,a.LocalId,c.StorAreaName from PB_BomMaterialReceive as a
left join PB_PackList as b on a.PackId=b.Id
left join View_StorAreaRackLocation as c on a.LocalId=c.Id
where a.DispatchDetailId='{0}' and a.PackId!='' and b.PackageType=1  and a.Status=0
group by a.PackId,DATE_FORMAT(a.CreateTime,'%Y-%m-%d'),a.LocalId,c.StorAreaName
) as t order by CreateTime desc";
            string sqlMain = string.Format(sql, dispatchdetailid);
            return await Db.GetListBySqlAsync<BomMaterialReceiveMaterialDTO>(sqlMain);
        }

        /// <summary>
        /// 组装件领料保存
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        [Transactional]
        public async Task SaveBomMaterialDataAsync(BomReceiveData data)
        {
            //包编号
            string packid = string.Empty;
            //物料编号
            string materialId = string.Empty;
            //成品编码
            string jobid = string.Empty;
            string packmodel = string.Empty;
            packmodel = data.PackQrCode.Split(',')?[0].ToString();
            if (packmodel == "13")
            {
                packid = data.PackQrCode.Split(',')?[1].ToString();
            }
            else if (packmodel == "04")
            {
                var model = data.PackQrCode.Split(',');
                if (model.Length > 2)
                {
                    materialId = model?[3].ToString();
                }
                else
                {
                    materialId = model?[1].ToString();
                }
                //合并重复物料指向新的物料Id
                PB_Material materdata = await Db.GetIQueryable<PB_Material>().Where(x => x.Id == materialId).FirstOrDefaultAsync();
                if (!materdata.TransferId.IsNullOrEmpty())
                {
                    materialId = materdata.TransferId;
                }
            }
            else if (packmodel == "14")
            {
                jobid = data.PackQrCode.Split(',')?[1].ToString();
            }

            Base_User bdata = await Db.GetIQueryable<Base_User>().Where(x => x.Id == _operator.UserId).FirstOrDefaultAsync();
            //派工单明细Id
            string dispatchdetailid = data.DispatchDetailQrCode.Split(',')?[1].ToString();

            TD_Receiving Recedata = await Db.GetIQueryable<TD_Receiving>().Where(x => x.RefCode == dispatchdetailid).FirstOrDefaultAsync();
            if (Recedata != null)
            {
                throw new BusException("已报工不能领料");
            }

            //获取派工单明细信息
            PB_DispatchDetail DispatchDetail = await Db.GetIQueryable<PB_DispatchDetail>().Where(x => x.Id == dispatchdetailid).FirstOrDefaultAsync();

            PB_ProductionOrder ProductionOrder = await Db.GetIQueryable<PB_ProductionOrder>().Where(x => x.Id == DispatchDetail.OrderId).FirstOrDefaultAsync();

            //货位编号
            string locaId = data.LocaQrCode.Split(',')?[1].ToString();

            PB_Location Location = await Db.GetIQueryable<PB_Location>().Where(x => x.Id == locaId).FirstOrDefaultAsync();

            string sqlMain = string.Empty;

            List<PB_BomMaterialReceive> BomMaterialReceiveList = new List<PB_BomMaterialReceive>();
            List<PB_StockLog> StockLogList = new List<PB_StockLog>();
            List<PB_Unpacking> UnpackingList = new List<PB_Unpacking>();
            List<PB_UnpackingDetail> UnpackingDetailList = new List<PB_UnpackingDetail>();

            if (packmodel == "13")
            {
                PB_PackList PackList = await Db.GetIQueryable<PB_PackList>().Where(x => x.Id == packid).FirstOrDefaultAsync();
                if (PackList.Status != 3)
                {
                    throw new BusException("请扫描仓库内的包!");
                }
                if (PackList.PackageType == 0)
                {
                    IT_LocalDetail LocalDetail = await Db.GetIQueryable<IT_LocalDetail>().Where(x => x.PackId == packid && x.LocalId == locaId).FirstOrDefaultAsync();
                    if (LocalDetail != null)
                    {
                        var trueorfalse = await _check.GetTrueOrFalse(LocalDetail.MaterialId, LocalDetail.StorId, locaId);
                        if (trueorfalse.boolType == false)
                        {
                            throw new BusException("仓库物料正在盘点中禁止下架出库");
                        }
                        //获取组装需要多少物料
                        Expression<Func<PB_DispatchDetail, Bom_MainNode, Bom_SubNode, BomSingleMaterialDTO>> select = (a, b, c) => new BomSingleMaterialDTO
                        {
                            DispatchDetailId = a.Id,
                            MaterialId = c.MaterailId,
                            MaterialName = c.MaterialName,
                            Nums = a.Nums * c.Quantity
                        };
                        select = select.BuildExtendSelectExpre();
                        var q = from a in Db.GetIQueryable<PB_DispatchDetail>().AsExpandable()
                                join b in Db.GetIQueryable<Bom_MainNode>() on a.MaterialId equals b.MaterailId into ab
                                from b in ab.DefaultIfEmpty()
                                join c in Db.GetIQueryable<Bom_SubNode>() on b.Id equals c.ParentId into bc
                                from c in bc.DefaultIfEmpty()
                                select @select.Invoke(a, b, c);
                        var BomSingleMaterial = await q.Where(x => x.DispatchDetailId == dispatchdetailid && x.MaterialId == LocalDetail.MaterialId).FirstOrDefaultAsync();
                        //获取已领取多少物料
                        List<PB_BomMaterialReceive> BomMaterialReceive = await Db.GetIQueryable<PB_BomMaterialReceive>().Where(x => x.DispatchDetailId == dispatchdetailid && x.MaterialId == LocalDetail.MaterialId).ToListAsync();
                        var BomMaterial = BomMaterialReceive.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, Nums = x.Sum(i => i.Nums) }).FirstOrDefault();

                        double? dnum = 0;
                        if (BomMaterial != null)
                        {
                            dnum = BomMaterial.Nums + LocalDetail.Num;
                        }
                        else
                        {
                            dnum = LocalDetail.Num;
                        }
                        if (dnum > (BomSingleMaterial == null ? 0 : BomSingleMaterial.Nums))
                        {
                            throw new BusException("物料数量大于派工单组装物料的剩余领取数量或请核对物料对应的包信息");
                        }
                        IT_LocalMaterial materdata = await Db.GetIQueryable<IT_LocalMaterial>().Where(x => x.MaterialId == LocalDetail.MaterialId && x.StorId == LocalDetail.StorId && x.LocalId == LocalDetail.LocalId).FirstOrDefaultAsync();
                        if (materdata == null)
                        {
                            throw new BusException("请检查库存物料是否存在");
                        }
                        else
                        {
                            dnum = materdata.Nums_O + materdata.Nums_N;
                            if (LocalDetail.Num > dnum)
                            {
                                throw new BusException("请检查库存物料数量是否满足!");
                            }
                        }
                        PB_Material Material = await Db.GetIQueryable<PB_Material>().Where(x => x.Id == LocalDetail.MaterialId).FirstOrDefaultAsync();

                        //添加领取记录
                        PB_BomMaterialReceive bmrdata = new PB_BomMaterialReceive();
                        bmrdata.Id = IdHelper.GetId();
                        bmrdata.DispatchId = DispatchDetail.ParentId;
                        bmrdata.DispatchDetailId = dispatchdetailid;
                        bmrdata.MaterialId = LocalDetail.MaterialId;
                        bmrdata.OrderId = DispatchDetail.OrderId;
                        bmrdata.OrderDetailId = DispatchDetail.OrderDetailId;
                        bmrdata.ProjectId = DispatchDetail.ProjectId;
                        bmrdata.DesignId = ProductionOrder.DesignId;
                        bmrdata.Nums = LocalDetail.Num;
                        bmrdata.Weight = Material.Weight;
                        bmrdata.CollectionTime = DateTime.Now;
                        bmrdata.CollectionUserId = _operator.UserId;
                        bmrdata.StorId = LocalDetail.StorId;
                        bmrdata.LocalId = LocalDetail.LocalId;
                        bmrdata.PackId = packid;
                        bmrdata.JobId = "";
                        bmrdata.Remarks = "";
                        bmrdata.Status = 0;
                        bmrdata.CreatorId = _operator.UserId;
                        bmrdata.CreateTime = DateTime.Now;
                        bmrdata.Deleted = false;
                        bmrdata.OldOrNew = LocalDetail.OldOrNew;
                        BomMaterialReceiveList.Add(bmrdata);

                        //增加出库日志
                        PB_StockLog pblog = new PB_StockLog();
                        pblog.Id = IdHelper.GetId();
                        pblog.PackId = packid;
                        pblog.MaterialId = LocalDetail.MaterialId;
                        pblog.StorId = LocalDetail.StorId;
                        pblog.LocationId = LocalDetail.LocalId;
                        pblog.StockNumber = LocalDetail.Num;
                        pblog.CreatorId = _operator.UserId;
                        pblog.CreatorName = bdata.RealName;
                        pblog.OperatorId = _operator.UserId;
                        pblog.Type = 1;
                        pblog.OrderID = "";
                        pblog.CreateTime = DateTime.Now;
                        pblog.State = 1;
                        pblog.JobId = "";
                        StockLogList.Add(pblog);

                        //删除库存明细表
                        sqlMain += @" delete from IT_LocalDetail where PackId='" + packid + "'; ";

                        //修改货架上存放货物存汇总表的审核前数量和新旧数量
                        if (LocalDetail.OldOrNew == 0)
                        {
                            sqlMain += @" update IT_LocalMaterial set Nums_O=IFNULL(Nums_O,0)-" + LocalDetail.Num + ",ScanNum=IFNULL(ScanNum,0)-" + LocalDetail.Num + " where StorId='" + LocalDetail.StorId + "' and LocalId='" + LocalDetail.LocalId + "' and MaterialId='" + LocalDetail.MaterialId + "'; ";
                        }
                        else if (LocalDetail.OldOrNew == 1)
                        {
                            sqlMain += @" update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)-" + LocalDetail.Num + ",ScanNum=IFNULL(ScanNum,0)-" + LocalDetail.Num + " where StorId='" + LocalDetail.StorId + "' and LocalId='" + LocalDetail.LocalId + "' and MaterialId='" + LocalDetail.MaterialId + "'; ";
                        }

                        sqlMain += @" update PB_PackList set Status=4,OutStorTime=NOW() where Id='" + packid + "'; ";
                    }
                    else
                    {
                        throw new BusException("请检查包是否在当前货位上");
                    }
                }
                else if (PackList.PackageType == 1)
                {
                    List<IT_LocalDetail> LocalDetail = await Db.GetIQueryable<IT_LocalDetail>().Where(x => x.PackId == packid && x.LocalId == locaId).ToListAsync();
                    //获取组装需要多少物料
                    Expression<Func<PB_DispatchDetail, Bom_MainNode, Bom_SubNode, BomSingleMaterialDTO>> select = (a, b, c) => new BomSingleMaterialDTO
                    {
                        DispatchDetailId = a.Id,
                        MaterialId = c.MaterailId,
                        MaterialName = c.MaterialName,
                        Nums = a.Nums * c.Quantity
                    };
                    select = select.BuildExtendSelectExpre();
                    var q = from a in Db.GetIQueryable<PB_DispatchDetail>().AsExpandable()
                            join b in Db.GetIQueryable<Bom_MainNode>() on a.MaterialId equals b.MaterailId into ab
                            from b in ab.DefaultIfEmpty()
                            join c in Db.GetIQueryable<Bom_SubNode>() on b.Id equals c.ParentId into bc
                            from c in bc.DefaultIfEmpty()
                            select @select.Invoke(a, b, c);
                    var BomSingleMaterial = await q.Where(x => x.DispatchDetailId == dispatchdetailid).ToListAsync();
                    //获取已领取多少物料
                    List<PB_BomMaterialReceive> BomMaterialReceive = await Db.GetIQueryable<PB_BomMaterialReceive>().Where(x => x.DispatchDetailId == dispatchdetailid).ToListAsync();
                    var BomMaterial = BomMaterialReceive.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, Nums = x.Sum(i => i.Nums) }).ToList();
                    foreach (var item in LocalDetail)
                    {
                        //获取需要组装的物料信息,看是否匹配
                        var itemdata = BomSingleMaterial.Where(x => x.MaterialId == item.MaterialId).FirstOrDefault();
                        if (itemdata != null)
                        {
                            //是否在盘点
                            var trueorfalse = await _check.GetTrueOrFalse(item.MaterialId, item.StorId, item.LocalId);
                            if (trueorfalse.boolType == false)
                            {
                                throw new BusException("仓库物料正在盘点中禁止下架出库");
                            }
                            //查询库存汇总信息
                            IT_LocalMaterial materdata = await Db.GetIQueryable<IT_LocalMaterial>().Where(x => x.MaterialId == item.MaterialId && x.StorId == item.StorId && x.LocalId == item.LocalId).FirstOrDefaultAsync();
                            if (materdata == null)
                            {
                                throw new BusException("请检查库存物料是否存在");
                            }
                            else
                            {
                                double? dnum = materdata.Nums_O + materdata.Nums_N;
                                if (item.Num > dnum)
                                {
                                    throw new BusException("请检查库存物料数量是否满足!");
                                }
                            }
                            var itemBomMaterial = BomMaterial.Where(x => x.MaterialId == item.MaterialId).FirstOrDefault();
                            double? bmnums = (itemBomMaterial == null ? 0 : itemBomMaterial.Nums) + item.Num;
                            if (bmnums > itemdata.Nums)
                            {
                                throw new BusException("物料数量大于派工单组装物料的剩余领取数量或请核对物料对应的包信息");
                            }

                            //修改货架上存放货物存汇总表的审核前数量和新旧数量
                            if (item.OldOrNew == 0)
                            {
                                sqlMain += @" update IT_LocalMaterial set Nums_O=IFNULL(Nums_O,0)-" + item.Num + ",ScanNum=IFNULL(ScanNum,0)-" + item.Num + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                            }
                            else if (item.OldOrNew == 1)
                            {
                                sqlMain += @" update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)-" + item.Num + ",ScanNum=IFNULL(ScanNum,0)-" + item.Num + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                            }
                            PB_Material Material = await Db.GetIQueryable<PB_Material>().Where(x => x.Id == item.MaterialId).FirstOrDefaultAsync();

                            //添加领取记录
                            PB_BomMaterialReceive bmrdata = new PB_BomMaterialReceive();
                            bmrdata.Id = IdHelper.GetId();
                            bmrdata.DispatchId = DispatchDetail.ParentId;
                            bmrdata.DispatchDetailId = dispatchdetailid;
                            bmrdata.MaterialId = item.MaterialId;
                            bmrdata.OrderId = DispatchDetail.OrderId;
                            bmrdata.OrderDetailId = DispatchDetail.OrderDetailId;
                            bmrdata.ProjectId = DispatchDetail.ProjectId;
                            bmrdata.DesignId = ProductionOrder.DesignId;
                            bmrdata.Nums = item.Num;
                            bmrdata.Weight = Material.Weight;
                            bmrdata.CollectionTime = DateTime.Now;
                            bmrdata.CollectionUserId = _operator.UserId;
                            bmrdata.StorId = Location.StorId;
                            bmrdata.LocalId = locaId;
                            bmrdata.PackId = packid;
                            bmrdata.JobId = "";
                            bmrdata.Remarks = "";
                            bmrdata.Status = 0;
                            bmrdata.CreatorId = _operator.UserId;
                            bmrdata.CreateTime = DateTime.Now;
                            bmrdata.Deleted = false;
                            bmrdata.OldOrNew = item.OldOrNew;
                            BomMaterialReceiveList.Add(bmrdata);

                            //增加出库日志
                            PB_StockLog pblog = new PB_StockLog();
                            pblog.Id = IdHelper.GetId();
                            pblog.PackId = packid;
                            pblog.MaterialId = item.MaterialId;
                            pblog.StorId = Location.StorId;
                            pblog.LocationId = locaId;
                            pblog.StockNumber = item.Num;
                            pblog.CreatorId = _operator.UserId;
                            pblog.CreatorName = bdata.RealName;
                            pblog.OperatorId = _operator.UserId;
                            pblog.Type = 1;
                            pblog.OrderID = "";
                            pblog.CreateTime = DateTime.Now;
                            pblog.State = 1;
                            pblog.JobId = "";
                            StockLogList.Add(pblog);
                        }
                        else
                        {
                            throw new BusException("请检查包里的物料是否与派工单组装物料BOM相匹配");
                        }
                    }

                    //删除库存明细表
                    sqlMain += @" delete from IT_LocalDetail where PackId='" + packid + "'; ";

                    //修改包的状态为已出库
                    sqlMain += @" update PB_PackList set Status=4,OutStorTime=NOW() where Id='" + packid + "'; ";

                }
            }
            else if (packmodel == "04")
            {
                //获取当前货位上物料信息
                List<IT_LocalDetail> LocalDetail = await Db.GetIQueryable<IT_LocalDetail>().Where(x => x.MaterialId == materialId && x.PackId == "" && x.LocalId == locaId).ToListAsync();
                if (LocalDetail.Count > 0)
                {
                    var trueorfalse = await _check.GetTrueOrFalse(materialId, Location.StorId, locaId);
                    if (trueorfalse.boolType == false)
                    {
                        throw new BusException("仓库物料正在盘点中禁止下架出库");
                    }

                    var LocalDetailList = LocalDetail.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, Nums = x.Sum(i => i.Num) }).FirstOrDefault();
                    if (LocalDetailList.Nums < data.Nums)
                    {
                        throw new BusException("请检查库存是否满足");
                    }

                    //获取组装需要多少物料
                    Expression<Func<PB_DispatchDetail, Bom_MainNode, Bom_SubNode, BomSingleMaterialDTO>> select = (a, b, c) => new BomSingleMaterialDTO
                    {
                        DispatchDetailId = a.Id,
                        MaterialId = c.MaterailId,
                        MaterialName = c.MaterialName,
                        Nums = a.Nums * c.Quantity
                    };
                    select = select.BuildExtendSelectExpre();
                    var q = from a in Db.GetIQueryable<PB_DispatchDetail>().AsExpandable()
                            join b in Db.GetIQueryable<Bom_MainNode>() on a.MaterialId equals b.MaterailId into ab
                            from b in ab.DefaultIfEmpty()
                            join c in Db.GetIQueryable<Bom_SubNode>() on b.Id equals c.ParentId into bc
                            from c in bc.DefaultIfEmpty()
                            select @select.Invoke(a, b, c);
                    var BomSingleMaterial = await q.Where(x => x.DispatchDetailId == dispatchdetailid && x.MaterialId == materialId).FirstOrDefaultAsync();
                    //获取已领取多少物料
                    List<PB_BomMaterialReceive> BomMaterialReceive = await Db.GetIQueryable<PB_BomMaterialReceive>().Where(x => x.DispatchDetailId == dispatchdetailid && x.MaterialId == materialId).ToListAsync();
                    var BomMaterial = BomMaterialReceive.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, Nums = x.Sum(i => i.Nums) }).FirstOrDefault();

                    double? dnum = 0;
                    if (BomMaterial != null)
                    {
                        dnum = BomMaterial.Nums + data.Nums;
                    }
                    else
                    {
                        dnum = data.Nums;
                    }
                    if (dnum > (BomSingleMaterial == null ? 0 : BomSingleMaterial.Nums))
                    {
                        throw new BusException("物料数量大于派工单组装物料的剩余领取数量或请核对物料对应的包信息");
                    }
                    //获取要出库的单个物料数量
                    double? warehouseNum = data.Nums;

                    foreach (var item in LocalDetail)
                    {
                        if (warehouseNum > 0)
                        {
                            //出库数量等于库存明细数量时
                            if (item.Num == warehouseNum)
                            {
                                warehouseNum -= item.Num;
                                if (item.OldOrNew == 0)
                                {
                                    sqlMain += @" update IT_LocalMaterial set Nums_O=IFNULL(Nums_O,0)-" + item.Num + ",ScanNum=IFNULL(ScanNum,0)-" + item.Num + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                                }
                                else if (item.OldOrNew == 1)
                                {
                                    sqlMain += @" update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)-" + item.Num + ",ScanNum=IFNULL(ScanNum,0)-" + item.Num + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                                }
                                sqlMain += @" delete from IT_LocalDetail where Id='" + item.Id + "'; ";

                            }
                            else if (item.Num < warehouseNum)   //出库数量大于库存明细数量时
                            {
                                if (warehouseNum >= item.Num)
                                {
                                    warehouseNum -= item.Num;
                                    if (item.OldOrNew == 0)
                                    {
                                        sqlMain += @" update IT_LocalMaterial set Nums_O=IFNULL(Nums_O,0)-" + item.Num + ",ScanNum=IFNULL(ScanNum,0)-" + item.Num + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                                    }
                                    else if (item.OldOrNew == 1)
                                    {
                                        sqlMain += @" update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)-" + item.Num + ",ScanNum=IFNULL(ScanNum,0)-" + item.Num + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                                    }
                                    sqlMain += @" delete from IT_LocalDetail where Id='" + item.Id + "'; ";
                                }
                                else
                                {
                                    if (item.OldOrNew == 0)
                                    {
                                        sqlMain += @" update IT_LocalMaterial set Nums_O=IFNULL(Nums_O,0)-" + warehouseNum + ",ScanNum=IFNULL(ScanNum,0)-" + warehouseNum + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                                    }
                                    else if (item.OldOrNew == 1)
                                    {
                                        sqlMain += @" update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)-" + warehouseNum + ",ScanNum=IFNULL(ScanNum,0)-" + warehouseNum + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                                    }
                                    sqlMain += @" update IT_LocalDetail set Num=IFNULL(Num,0)-" + warehouseNum + " where Id='" + item.Id + "'; ";
                                    warehouseNum -= warehouseNum;
                                }
                            }
                            else if (item.Num > warehouseNum) //出库数量小于库存明细数量时
                            {
                                item.Num -= warehouseNum;
                                if (item.OldOrNew == 0)
                                {
                                    sqlMain += @" update IT_LocalMaterial set Nums_O=IFNULL(Nums_O,0)-" + warehouseNum + ",ScanNum=IFNULL(ScanNum,0)-" + warehouseNum + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                                }
                                else if (item.OldOrNew == 1)
                                {
                                    sqlMain += @" update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)-" + warehouseNum + ",ScanNum=IFNULL(ScanNum,0)-" + warehouseNum + " where StorId='" + item.StorId + "' and LocalId='" + item.LocalId + "' and MaterialId='" + item.MaterialId + "'; ";
                                }
                                sqlMain += @" update IT_LocalDetail set Num=IFNULL(Num,0)-" + warehouseNum + " where Id='" + item.Id + "'; ";
                                warehouseNum -= warehouseNum;
                            }

                        }
                        else
                        {
                            continue;
                        }
                    }

                    PB_Material Material = await Db.GetIQueryable<PB_Material>().Where(x => x.Id == materialId).FirstOrDefaultAsync();

                    //添加领取记录
                    PB_BomMaterialReceive bmrdata = new PB_BomMaterialReceive();
                    bmrdata.Id = IdHelper.GetId();
                    bmrdata.DispatchId = DispatchDetail.ParentId;
                    bmrdata.DispatchDetailId = dispatchdetailid;
                    bmrdata.MaterialId = materialId;
                    bmrdata.OrderId = DispatchDetail.OrderId;
                    bmrdata.OrderDetailId = DispatchDetail.OrderDetailId;
                    bmrdata.ProjectId = DispatchDetail.ProjectId;
                    bmrdata.DesignId = ProductionOrder.DesignId;
                    bmrdata.Nums = data.Nums;
                    bmrdata.Weight = Material.Weight;
                    bmrdata.CollectionTime = DateTime.Now;
                    bmrdata.CollectionUserId = _operator.UserId;
                    bmrdata.StorId = Location.StorId;
                    bmrdata.LocalId = locaId;
                    bmrdata.PackId = "";
                    bmrdata.JobId = "";
                    bmrdata.Remarks = "";
                    bmrdata.Status = 0;
                    bmrdata.CreatorId = _operator.UserId;
                    bmrdata.CreateTime = DateTime.Now;
                    bmrdata.Deleted = false;
                    bmrdata.OldOrNew = 1;
                    BomMaterialReceiveList.Add(bmrdata);

                    //增加出库日志
                    PB_StockLog pblog = new PB_StockLog();
                    pblog.Id = IdHelper.GetId();
                    pblog.PackId = "";
                    pblog.MaterialId = materialId;
                    pblog.StorId = Location.StorId;
                    pblog.LocationId = locaId;
                    pblog.StockNumber = data.Nums;
                    pblog.CreatorId = _operator.UserId;
                    pblog.CreatorName = bdata.RealName;
                    pblog.OperatorId = _operator.UserId;
                    pblog.Type = 1;
                    pblog.OrderID = "";
                    pblog.CreateTime = DateTime.Now;
                    pblog.State = 1;
                    pblog.JobId = "";
                    StockLogList.Add(pblog);

                }
                else
                {
                    throw new BusException("请检查物料是否在当前货位上");
                }
            }
            else if (packmodel == "14")
            {
                IT_LocalDetail LocalDetail = await Db.GetIQueryable<IT_LocalDetail>().Where(x => x.JobId == jobid).FirstOrDefaultAsync();
                if (LocalDetail != null)
                {
                    //获取成品信息
                    PB_JobBookingQrCode jobdata = await Db.GetIQueryable<PB_JobBookingQrCode>().Where(x => x.Id == jobid).FirstOrDefaultAsync();
                    if (jobdata == null)
                    {
                        throw new BusException("请检查成品码是否有效");
                    }
                    //获取组装需要多少物料
                    Expression<Func<PB_DispatchDetail, Bom_MainNode, Bom_SubNode, BomSingleMaterialDTO>> select = (a, b, c) => new BomSingleMaterialDTO
                    {
                        DispatchDetailId = a.Id,
                        MaterialId = c.MaterailId,
                        MaterialName = c.MaterialName,
                        Nums = a.Nums * c.Quantity
                    };
                    select = select.BuildExtendSelectExpre();
                    var q = from a in Db.GetIQueryable<PB_DispatchDetail>().AsExpandable()
                            join b in Db.GetIQueryable<Bom_MainNode>() on a.MaterialId equals b.MaterailId into ab
                            from b in ab.DefaultIfEmpty()
                            join c in Db.GetIQueryable<Bom_SubNode>() on b.Id equals c.ParentId into bc
                            from c in bc.DefaultIfEmpty()
                            select @select.Invoke(a, b, c);
                    var BomSingleMaterial = await q.Where(x => x.DispatchDetailId == dispatchdetailid && x.MaterialId == jobdata.MaterialId).FirstOrDefaultAsync();
                    if (BomSingleMaterial == null)
                    {
                        throw new BusException("请检查扫描的成品是否与组装的需要的物料匹配");
                    }
                    //获取已领取多少物料
                    List<PB_BomMaterialReceive> BomMaterialReceive = await Db.GetIQueryable<PB_BomMaterialReceive>().Where(x => x.DispatchDetailId == dispatchdetailid && x.MaterialId == jobdata.MaterialId).ToListAsync();
                    var BomMaterial = BomMaterialReceive.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, Nums = x.Sum(i => i.Nums) }).FirstOrDefault();

                    double? dnum = 0;
                    if (BomMaterial==null)
                    {
                        dnum = 1;
                    }
                    else
                    {
                        dnum = BomMaterial.Nums + 1;
                    }
                    if (dnum > BomSingleMaterial.Nums)
                    {
                        throw new BusException("物料数量大于组装物料的剩余领取数量");
                    }
                    var trueorfalse = await _check.GetTrueOrFalse(LocalDetail.MaterialId, LocalDetail.StorId, locaId);
                    if (trueorfalse.boolType == false)
                    {
                        throw new BusException("仓库物料正在盘点中禁止下架出库");
                    }
                    IT_LocalMaterial materdata = await Db.GetIQueryable<IT_LocalMaterial>().Where(x => x.MaterialId == LocalDetail.MaterialId && x.StorId == LocalDetail.StorId && x.LocalId == LocalDetail.LocalId).FirstOrDefaultAsync();
                    if (materdata == null)
                    {
                        throw new BusException("请检查库存物料是否存在");
                    }
                    //修改库存汇总数量
                    if (LocalDetail.OldOrNew == 0)
                    {
                        sqlMain += @"update IT_LocalMaterial set Nums_O=IFNULL(Nums_O,0)-1,ScanNum=IFNULL(ScanNum,0)-1 where StorId='" + LocalDetail.StorId + "' and LocalId='" + LocalDetail.LocalId + "' and MaterialId='" + LocalDetail.MaterialId + "'; ";
                    }
                    else if (LocalDetail.OldOrNew == 1)
                    {
                        sqlMain += @"update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)-1,ScanNum=IFNULL(ScanNum,0)-1 where StorId='" + LocalDetail.StorId + "' and LocalId='" + LocalDetail.LocalId + "' and MaterialId='" + LocalDetail.MaterialId + "'; ";
                    }
                    //修改库存明细成品对应包的数量
                    sqlMain += @" delete from IT_LocalDetail where JobId='" + jobid + "'; ";
                    PB_Material Material = await Db.GetIQueryable<PB_Material>().Where(x => x.Id == LocalDetail.MaterialId).FirstOrDefaultAsync();

                    //添加领取记录
                    PB_BomMaterialReceive bmrdata = new PB_BomMaterialReceive();
                    bmrdata.Id = IdHelper.GetId();
                    bmrdata.DispatchId = DispatchDetail.ParentId;
                    bmrdata.DispatchDetailId = dispatchdetailid;
                    bmrdata.MaterialId = LocalDetail.MaterialId;
                    bmrdata.OrderId = DispatchDetail.OrderId;
                    bmrdata.OrderDetailId = DispatchDetail.OrderDetailId;
                    bmrdata.ProjectId = DispatchDetail.ProjectId;
                    bmrdata.DesignId = ProductionOrder.DesignId;
                    bmrdata.Nums = 1;
                    bmrdata.Weight = Material.Weight;
                    bmrdata.CollectionTime = DateTime.Now;
                    bmrdata.CollectionUserId = _operator.UserId;
                    bmrdata.StorId = Location.StorId;
                    bmrdata.LocalId = locaId;
                    bmrdata.PackId = "";
                    bmrdata.JobId = jobid;
                    bmrdata.Remarks = "";
                    bmrdata.Status = 0;
                    bmrdata.CreatorId = _operator.UserId;
                    bmrdata.CreateTime = DateTime.Now;
                    bmrdata.Deleted = false;
                    bmrdata.OldOrNew = LocalDetail.OldOrNew;
                    BomMaterialReceiveList.Add(bmrdata);

                    //增加出库日志
                    PB_StockLog pblog = new PB_StockLog();
                    pblog.Id = IdHelper.GetId();
                    pblog.PackId = "";
                    pblog.MaterialId = materialId;
                    pblog.StorId = Location.StorId;
                    pblog.LocationId = locaId;
                    pblog.StockNumber = 1;
                    pblog.CreatorId = _operator.UserId;
                    pblog.CreatorName = bdata.RealName;
                    pblog.OperatorId = _operator.UserId;
                    pblog.Type = 1;
                    pblog.OrderID = "";
                    pblog.CreateTime = DateTime.Now;
                    pblog.State = 1;
                    pblog.JobId = jobid;
                    StockLogList.Add(pblog);

                }
                else
                {
                    //获取成品信息
                    PB_JobBookingQrCode jobdata = await Db.GetIQueryable<PB_JobBookingQrCode>().Where(x => x.Id == jobid).FirstOrDefaultAsync();
                    if (jobdata == null)
                    {
                        throw new BusException("请检查成品码是否有效");
                    }
                    if (jobdata.PackId.IsNullOrEmpty())
                    {
                        throw new BusException("请检查成品是否在包内");
                    }
                    //获取组装需要多少物料
                    Expression<Func<PB_DispatchDetail, Bom_MainNode, Bom_SubNode, BomSingleMaterialDTO>> select = (a, b, c) => new BomSingleMaterialDTO
                    {
                        DispatchDetailId = a.Id,
                        MaterialId = c.MaterailId,
                        MaterialName = c.MaterialName,
                        Nums = a.Nums * c.Quantity
                    };
                    select = select.BuildExtendSelectExpre();
                    var q = from a in Db.GetIQueryable<PB_DispatchDetail>().AsExpandable()
                            join b in Db.GetIQueryable<Bom_MainNode>() on a.MaterialId equals b.MaterailId into ab
                            from b in ab.DefaultIfEmpty()
                            join c in Db.GetIQueryable<Bom_SubNode>() on b.Id equals c.ParentId into bc
                            from c in bc.DefaultIfEmpty()
                            select @select.Invoke(a, b, c);
                    var BomSingleMaterial = await q.Where(x => x.DispatchDetailId == dispatchdetailid && x.MaterialId == jobdata.MaterialId).FirstOrDefaultAsync();
                    if (BomSingleMaterial == null)
                    {
                        throw new BusException("请检查扫描的成品是否与组装的需要的物料匹配");
                    }
                    //获取已领取多少物料
                    List<PB_BomMaterialReceive> BomMaterialReceive = await Db.GetIQueryable<PB_BomMaterialReceive>().Where(x => x.DispatchDetailId == dispatchdetailid && x.MaterialId == jobdata.MaterialId).ToListAsync();
                    var BomMaterial = BomMaterialReceive.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, Nums = x.Sum(i => i.Nums) }).FirstOrDefault();

                    double? dnum = 0;
                    if (BomMaterial == null)
                    {
                        dnum = 1;
                    }
                    else
                    {
                        dnum = BomMaterial.Nums + 1;
                    }
                    if (dnum > BomSingleMaterial.Nums)
                    {
                        throw new BusException("物料数量大于组装物料的剩余领取数量");
                    }

                    PB_PackList packdata = await Db.GetIQueryable<PB_PackList>().Where(x => x.Id == jobdata.PackId).FirstOrDefaultAsync();

                    if (packdata.Nums == 1)
                    {
                        throw new BusException("包不允许拆包,请扫描包出库");
                    }
                    #region  默认自动拆包

                    //拆包日志主表
                    PB_Unpacking unpack = new PB_Unpacking();
                    string unpackid = IdHelper.GetId();
                    unpack.Id = unpackid;
                    unpack.PrimaryPackId = jobdata.PackId;
                    unpack.Nums = 1;
                    unpack.OperatorUserId = _operator.UserId;
                    unpack.OperatorUserName = bdata.RealName;
                    unpack.CreateTime = DateTime.Now;
                    unpack.Deleted = false;
                    unpack.Type = 2;
                    UnpackingList.Add(unpack);

                    //拆包日志明细表
                    PB_UnpackingDetail undetail = new PB_UnpackingDetail();
                    undetail.Id = IdHelper.GetId();
                    undetail.ParentId = unpackid;
                    undetail.JobBookingId = jobid;
                    undetail.OldOrNew = jobdata.OldOrNew;
                    undetail.CreatorId = _operator.UserId;
                    undetail.CreatorRealName = bdata.RealName;
                    undetail.CreateTime = DateTime.Now;
                    undetail.Deleted = false;
                    undetail.PickMainId = "";
                    undetail.PickListId = "";
                    undetail.MaterialId = jobdata.MaterialId;
                    UnpackingDetailList.Add(undetail);


                    //删除成品与包的关系
                    sqlMain += @" update PB_JobBookingQrCode set PackId='' where Id='" + jobid + "'; ";
                    //删除包明细与成品的关系
                    sqlMain += @" DELETE from PB_PackListInfo where PackId='" + jobdata.PackId + "' and JobBookingId='" + jobid + "'; ";
                    //修改包的包装数量
                    sqlMain += @" update PB_PackList set Nums=IFNULL(Nums,0)-1 where Id='" + jobdata.PackId + "'; ";
                    //修改包的包装数量
                    sqlMain += @" update PB_PackMaterialCount set Nums=IFNULL(Nums,0)-1 where PackId='" + jobdata.PackId + "' and MaterialId='" + jobdata.MaterialId + "'; ";

                    //await ExecuteSqlAsync(sqlMain);
                    #endregion

                    //查询仓库包信息
                    IT_LocalDetail LocalDetailPack = await Db.GetIQueryable<IT_LocalDetail>().Where(x => x.PackId == jobdata.PackId && x.LocalId == locaId).FirstOrDefaultAsync();
                    if (LocalDetailPack != null)
                    {
                        var trueorfalse = await _check.GetTrueOrFalse(LocalDetailPack.MaterialId, LocalDetailPack.StorId, locaId);
                        if (trueorfalse.boolType == false)
                        {
                            throw new BusException("仓库物料正在盘点中禁止下架出库");
                        }
                        IT_LocalMaterial materdata = await Db.GetIQueryable<IT_LocalMaterial>().Where(x => x.MaterialId == LocalDetailPack.MaterialId && x.StorId == LocalDetailPack.StorId && x.LocalId == LocalDetailPack.LocalId).FirstOrDefaultAsync();
                        if (materdata == null)
                        {
                            throw new BusException("请检查库存物料是否存在");
                        }
                        //修改库存汇总数量
                        if (LocalDetailPack.OldOrNew == 0)
                        {
                            sqlMain += @"update IT_LocalMaterial set Nums_O=IFNULL(Nums_O,0)-1,ScanNum=IFNULL(ScanNum,0)-1 where StorId='" + LocalDetailPack.StorId + "' and LocalId='" + LocalDetailPack.LocalId + "' and MaterialId='" + LocalDetailPack.MaterialId + "'; ";
                        }
                        else if (LocalDetailPack.OldOrNew == 1)
                        {
                            sqlMain += @"update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)-1,ScanNum=IFNULL(ScanNum,0)-1 where StorId='" + LocalDetailPack.StorId + "' and LocalId='" + LocalDetailPack.LocalId + "' and MaterialId='" + LocalDetailPack.MaterialId + "'; ";
                        }
                        //修改物料档案审核前数量
                        sqlMain += @" update PB_Material set ScanNum=IFNULL(ScanNum,0)-1 where Id='" + LocalDetailPack.MaterialId + "'; ";
                        //修改库存明细成品对应包的数量
                        sqlMain += @" update IT_LocalDetail set Num=IFNULL(Num,0)-1 where PackId='" + LocalDetailPack.PackId + "' and LocalId='" + LocalDetailPack.LocalId + "'; ";

                        PB_Material Material = await Db.GetIQueryable<PB_Material>().Where(x => x.Id == LocalDetailPack.MaterialId).FirstOrDefaultAsync();
                        //添加领取记录
                        PB_BomMaterialReceive bmrdata = new PB_BomMaterialReceive();
                        bmrdata.Id = IdHelper.GetId();
                        bmrdata.DispatchId = DispatchDetail.ParentId;
                        bmrdata.DispatchDetailId = dispatchdetailid;
                        bmrdata.MaterialId = LocalDetailPack.MaterialId;
                        bmrdata.OrderId = DispatchDetail.OrderId;
                        bmrdata.OrderDetailId = DispatchDetail.OrderDetailId;
                        bmrdata.ProjectId = DispatchDetail.ProjectId;
                        bmrdata.DesignId = ProductionOrder.DesignId;
                        bmrdata.Nums = 1;
                        bmrdata.Weight = Material.Weight;
                        bmrdata.CollectionTime = DateTime.Now;
                        bmrdata.CollectionUserId = _operator.UserId;
                        bmrdata.StorId = Location.StorId;
                        bmrdata.LocalId = locaId;
                        bmrdata.PackId = "";
                        bmrdata.JobId = jobid;
                        bmrdata.Remarks = "";
                        bmrdata.Status = 0;
                        bmrdata.CreatorId = _operator.UserId;
                        bmrdata.CreateTime = DateTime.Now;
                        bmrdata.Deleted = false;
                        bmrdata.OldOrNew = LocalDetailPack.OldOrNew;
                        BomMaterialReceiveList.Add(bmrdata);

                        //增加出库日志
                        PB_StockLog pblog = new PB_StockLog();
                        pblog.Id = IdHelper.GetId();
                        pblog.PackId = "";
                        pblog.MaterialId = LocalDetailPack.MaterialId;
                        pblog.StorId = Location.StorId;
                        pblog.LocationId = locaId;
                        pblog.StockNumber = 1;
                        pblog.CreatorId = _operator.UserId;
                        pblog.CreatorName = bdata.RealName;
                        pblog.OperatorId = _operator.UserId;
                        pblog.Type = 1;
                        pblog.OrderID = "";
                        pblog.CreateTime = DateTime.Now;
                        pblog.State = 1;
                        pblog.JobId = jobid;
                        pblog.OldOrNew = LocalDetailPack.OldOrNew;
                        StockLogList.Add(pblog);

                    }
                    else
                    {
                        throw new BusException("请扫描正确的货位上的成品信息");
                    }
                }
            }

            await Db.InsertAsync<PB_BomMaterialReceive>(BomMaterialReceiveList);

            await Db.InsertAsync<PB_StockLog>(StockLogList);

            await Db.InsertAsync<PB_UnpackingDetail>(UnpackingDetailList);

            await Db.InsertAsync<PB_Unpacking>(UnpackingList);

            await ExecuteSqlAsync(sqlMain);

        }

        /// <summary>
        /// 组装件领料移除
        /// </summary>
        /// <param name="data"></param>
        /// <param name="DispatchDetailQrCode">派工单明细二维码</param>
        /// <returns></returns>
        [Transactional]
        public async Task DeleteBomMaterialReceiveAsync(BomMaterialReceiveMaterialDTO data,string DispatchDetailQrCode)
        {
            string dispatchdetailid = DispatchDetailQrCode.Split(',')?[1].ToString();
            Base_User userdata = await Db.GetIQueryable<Base_User>().Where(x => x.Id == _operator.UserId).FirstOrDefaultAsync();
            List<IT_LocalDetail> LocalDetailList = new List<IT_LocalDetail>();
            List<PB_StockLog> StockLogList = new List<PB_StockLog>();
            List<IT_LocalMaterial> LocalMaterialList = new List<IT_LocalMaterial>();
            string sqlMain = string.Empty;
            if (!data.PackId.IsNullOrEmpty())
            {
                //获取包的信息
                List<PB_BomMaterialReceive> BomMaterialReceive = await Db.GetIQueryable<PB_BomMaterialReceive>().Where(x => x.DispatchDetailId == dispatchdetailid && x.PackId == data.PackId).ToListAsync();
                foreach (var item in BomMaterialReceive)
                {
                    IT_LocalDetail LocalDetail = new IT_LocalDetail();
                    LocalDetail.Id = IdHelper.GetId();
                    LocalDetail.StorId = item.StorId;
                    LocalDetail.LocalId = item.LocalId;
                    LocalDetail.MaterialId = item.MaterialId;
                    LocalDetail.BarCode = h_Pack + "," + item.PackId;
                    LocalDetail.InTime = DateTime.Now;
                    LocalDetail.CreateTime = DateTime.Now;
                    LocalDetail.CreatorId = _operator.UserId;
                    LocalDetail.Deleted = false;
                    LocalDetail.Num = item.Nums;
                    LocalDetail.PackId = item.PackId;
                    LocalDetail.OldOrNew = item.OldOrNew;
                    LocalDetail.RecId = "";
                    LocalDetail.JobId = "";
                    LocalDetailList.Add(LocalDetail);

                    PB_StockLog StockLog = new PB_StockLog();
                    StockLog.Id = IdHelper.GetId();
                    StockLog.PackId = item.PackId;
                    StockLog.MaterialId = item.MaterialId;
                    StockLog.StorId = item.StorId;
                    StockLog.LocationId = item.LocalId;
                    StockLog.StockNumber = item.Nums;
                    StockLog.CreatorId = _operator.UserId;
                    StockLog.Deleted = false;
                    StockLog.CreatorName = userdata.RealName;
                    StockLog.Type = 1;
                    StockLog.CreateTime = DateTime.Now;
                    StockLog.State = 1;
                    StockLog.OldOrNew = item.OldOrNew;
                    StockLog.CirBillId = "";
                    StockLog.JobId = "";
                    StockLogList.Add(StockLog);

                    IT_LocalMaterial LocalMaterial = await Db.GetIQueryable<IT_LocalMaterial>().Where(x => x.MaterialId == item.MaterialId && x.LocalId == item.LocalId).FirstOrDefaultAsync();
                    var LocalMaterialdata = LocalMaterialList.Where(x => x.MaterialId == item.MaterialId && x.LocalId == item.LocalId).FirstOrDefault();
                    if (LocalMaterial != null && LocalMaterialdata != null)
                    {
                        IT_LocalMaterial itemdata = new IT_LocalMaterial();
                        itemdata.Id = IdHelper.GetId();
                        itemdata.MaterialId = item.MaterialId;
                        itemdata.StorId = item.StorId;
                        itemdata.LocalId = item.LocalId;
                        if (item.OldOrNew == 0)
                        {
                            itemdata.Nums_O = Convert.ToDouble(item.Nums);
                            itemdata.Nums_N = 0;
                        }
                        else if (item.OldOrNew == 1)
                        {
                            itemdata.Nums_O = 0;
                            itemdata.Nums_N = Convert.ToDouble(item.Nums);
                        }
                        itemdata.Nums_R = 0;
                        itemdata.CreatorId = _operator.UserId;
                        itemdata.CreateTime = DateTime.Now;
                        itemdata.Deleted = false;
                        itemdata.ScanNum = Convert.ToDouble(item.Nums);
                        itemdata.CheckNum = 0;
                        LocalMaterialList.Add(itemdata);
                    }
                    else
                    {
                        if (item.OldOrNew == 0)
                        {
                            sqlMain += @" update IT_LocalMaterial set Nums_O=IFNULL(Nums_O,0)+" + item.Nums + ",ScanNum=IFNULL(ScanNum,0)+" + item.Nums + " where MaterialId='" + item.MaterialId + "' and LocalId='" + item.LocalId + "'; ";
                        } 
                        else if (item.OldOrNew == 1)
                        {
                            sqlMain += @" update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)+" + item.Nums + ",ScanNum=IFNULL(ScanNum,0)+" + item.Nums + " where MaterialId='" + item.MaterialId + "' and LocalId='" + item.LocalId + "'; ";
                        }
                    }
                    sqlMain += @" delete from PB_BomMaterialReceive where Id='" + item.Id + "'; ";
                }
            }
            else
            {
                if (!data.JobId.IsNullOrEmpty())
                {
                    PB_BomMaterialReceive BomMaterialReceive = await Db.GetIQueryable<PB_BomMaterialReceive>().Where(x => x.DispatchDetailId == dispatchdetailid && x.JobId == data.JobId).FirstOrDefaultAsync();
                    IT_LocalDetail LocalDetail = new IT_LocalDetail();
                    LocalDetail.Id = IdHelper.GetId();
                    LocalDetail.StorId = BomMaterialReceive.StorId;
                    LocalDetail.LocalId = BomMaterialReceive.LocalId;
                    LocalDetail.MaterialId = BomMaterialReceive.MaterialId;
                    LocalDetail.BarCode = h_FinishedProduct + "," + BomMaterialReceive.JobId;
                    LocalDetail.InTime = DateTime.Now;
                    LocalDetail.CreateTime = DateTime.Now;
                    LocalDetail.CreatorId = _operator.UserId;
                    LocalDetail.Deleted = false;
                    LocalDetail.Num = BomMaterialReceive.Nums;
                    LocalDetail.PackId = "";
                    LocalDetail.OldOrNew = BomMaterialReceive.OldOrNew;
                    LocalDetail.RecId = "";
                    LocalDetail.JobId = data.JobId;
                    LocalDetailList.Add(LocalDetail);

                    PB_StockLog StockLog = new PB_StockLog();
                    StockLog.Id = IdHelper.GetId();
                    StockLog.PackId = BomMaterialReceive.PackId;
                    StockLog.MaterialId = BomMaterialReceive.MaterialId;
                    StockLog.StorId = BomMaterialReceive.StorId;
                    StockLog.LocationId = BomMaterialReceive.LocalId;
                    StockLog.StockNumber = BomMaterialReceive.Nums;
                    StockLog.CreatorId = _operator.UserId;
                    StockLog.Deleted = false;
                    StockLog.CreatorName = userdata.RealName;
                    StockLog.Type = 1;
                    StockLog.CreateTime = DateTime.Now;
                    StockLog.State = 1;
                    StockLog.OldOrNew = BomMaterialReceive.OldOrNew;
                    StockLog.CirBillId = "";
                    StockLog.JobId = data.JobId;
                    StockLogList.Add(StockLog);

                    IT_LocalMaterial LocalMaterial = await Db.GetIQueryable<IT_LocalMaterial>().Where(x => x.MaterialId == BomMaterialReceive.MaterialId && x.LocalId == BomMaterialReceive.LocalId).FirstOrDefaultAsync();
                    var LocalMaterialdata = LocalMaterialList.Where(x => x.MaterialId == BomMaterialReceive.MaterialId && x.LocalId == BomMaterialReceive.LocalId).FirstOrDefault();
                    if (LocalMaterial != null && LocalMaterialdata != null)
                    {
                        IT_LocalMaterial itemdata = new IT_LocalMaterial();
                        itemdata.Id = IdHelper.GetId();
                        itemdata.MaterialId = BomMaterialReceive.MaterialId;
                        itemdata.StorId = BomMaterialReceive.StorId;
                        itemdata.LocalId = BomMaterialReceive.LocalId;
                        if (BomMaterialReceive.OldOrNew == 0)
                        {
                            itemdata.Nums_O = Convert.ToDouble(BomMaterialReceive.Nums);
                            itemdata.Nums_N = 0;
                        }
                        else if (BomMaterialReceive.OldOrNew == 1)
                        {
                            itemdata.Nums_O = 0;
                            itemdata.Nums_N = Convert.ToDouble(BomMaterialReceive.Nums);
                        }
                        itemdata.Nums_R = 0;
                        itemdata.CreatorId = _operator.UserId;
                        itemdata.CreateTime = DateTime.Now;
                        itemdata.Deleted = false;
                        itemdata.ScanNum = Convert.ToDouble(BomMaterialReceive.Nums);
                        itemdata.CheckNum = 0;
                        LocalMaterialList.Add(itemdata);
                    }
                    else
                    {
                        if (BomMaterialReceive.OldOrNew == 0)
                        {
                            sqlMain += @" update IT_LocalMaterial set Nums_O=IFNULL(Nums_O,0)+" + BomMaterialReceive.Nums + ",ScanNum=IFNULL(ScanNum,0)+" + BomMaterialReceive.Nums + " where MaterialId='" + BomMaterialReceive.MaterialId + "' and LocalId='" + BomMaterialReceive.LocalId + "'; ";
                        }
                        else if (BomMaterialReceive.OldOrNew == 1)
                        {
                            sqlMain += @" update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)+" + BomMaterialReceive.Nums + ",ScanNum=IFNULL(ScanNum,0)+" + BomMaterialReceive.Nums + " where MaterialId='" + BomMaterialReceive.MaterialId + "' and LocalId='" + BomMaterialReceive.LocalId + "'; ";
                        }
                    }
                    sqlMain += @" delete from PB_BomMaterialReceive where Id='" + BomMaterialReceive.Id + "'; ";
                }
                else
                {
                    PB_BomMaterialReceive BomMaterialReceive = await Db.GetIQueryable<PB_BomMaterialReceive>().Where(x => x.DispatchDetailId == dispatchdetailid && x.MaterialId == data.MaterialId && x.Nums == data.Nums).OrderByDescending(x => x.CreateTime).FirstOrDefaultAsync();
                    IT_LocalDetail LocalDetail = new IT_LocalDetail();
                    LocalDetail.Id = IdHelper.GetId();
                    LocalDetail.StorId = BomMaterialReceive.StorId;
                    LocalDetail.LocalId = BomMaterialReceive.LocalId;
                    LocalDetail.MaterialId = BomMaterialReceive.MaterialId;
                    LocalDetail.BarCode = h_FinishedProduct + "," + BomMaterialReceive.JobId;
                    LocalDetail.InTime = DateTime.Now;
                    LocalDetail.CreateTime = DateTime.Now;
                    LocalDetail.CreatorId = _operator.UserId;
                    LocalDetail.Deleted = false;
                    LocalDetail.Num = BomMaterialReceive.Nums;
                    LocalDetail.PackId = "";
                    LocalDetail.OldOrNew = BomMaterialReceive.OldOrNew;
                    LocalDetail.RecId = "";
                    LocalDetail.JobId = "";
                    LocalDetailList.Add(LocalDetail);

                    PB_StockLog StockLog = new PB_StockLog();
                    StockLog.Id = IdHelper.GetId();
                    StockLog.PackId = BomMaterialReceive.PackId;
                    StockLog.MaterialId = BomMaterialReceive.MaterialId;
                    StockLog.StorId = BomMaterialReceive.StorId;
                    StockLog.LocationId = BomMaterialReceive.LocalId;
                    StockLog.StockNumber = BomMaterialReceive.Nums;
                    StockLog.CreatorId = _operator.UserId;
                    StockLog.Deleted = false;
                    StockLog.CreatorName = userdata.RealName;
                    StockLog.Type = 1;
                    StockLog.CreateTime = DateTime.Now;
                    StockLog.State = 1;
                    StockLog.OldOrNew = BomMaterialReceive.OldOrNew;
                    StockLog.CirBillId = "";
                    StockLog.JobId = "";
                    StockLogList.Add(StockLog);

                    IT_LocalMaterial LocalMaterial = await Db.GetIQueryable<IT_LocalMaterial>().Where(x => x.MaterialId == BomMaterialReceive.MaterialId && x.LocalId == BomMaterialReceive.LocalId).FirstOrDefaultAsync();
                    var LocalMaterialdata = LocalMaterialList.Where(x => x.MaterialId == BomMaterialReceive.MaterialId && x.LocalId == BomMaterialReceive.LocalId).FirstOrDefault();
                    if (LocalMaterial != null && LocalMaterialdata != null)
                    {
                        IT_LocalMaterial itemdata = new IT_LocalMaterial();
                        itemdata.Id = IdHelper.GetId();
                        itemdata.MaterialId = BomMaterialReceive.MaterialId;
                        itemdata.StorId = BomMaterialReceive.StorId;
                        itemdata.LocalId = BomMaterialReceive.LocalId;
                        if (BomMaterialReceive.OldOrNew == 0)
                        {
                            itemdata.Nums_O = Convert.ToDouble(BomMaterialReceive.Nums);
                            itemdata.Nums_N = 0;
                        }
                        else if (BomMaterialReceive.OldOrNew == 1)
                        {
                            itemdata.Nums_O = 0;
                            itemdata.Nums_N = Convert.ToDouble(BomMaterialReceive.Nums);
                        }
                        itemdata.Nums_R = 0;
                        itemdata.CreatorId = _operator.UserId;
                        itemdata.CreateTime = DateTime.Now;
                        itemdata.Deleted = false;
                        itemdata.ScanNum = Convert.ToDouble(BomMaterialReceive.Nums);
                        itemdata.CheckNum = 0;
                        LocalMaterialList.Add(itemdata);
                    }
                    else
                    {
                        if (BomMaterialReceive.OldOrNew == 0)
                        {
                            sqlMain += @" update IT_LocalMaterial set Nums_O=IFNULL(Nums_O,0)+" + BomMaterialReceive.Nums + ",ScanNum=IFNULL(ScanNum,0)+" + BomMaterialReceive.Nums + " where MaterialId='" + BomMaterialReceive.MaterialId + "' and LocalId='" + BomMaterialReceive.LocalId + "'; ";
                        }
                        else if (BomMaterialReceive.OldOrNew == 1)
                        {
                            sqlMain += @" update IT_LocalMaterial set Nums_N=IFNULL(Nums_N,0)+" + BomMaterialReceive.Nums + ",ScanNum=IFNULL(ScanNum,0)+" + BomMaterialReceive.Nums + " where MaterialId='" + BomMaterialReceive.MaterialId + "' and LocalId='" + BomMaterialReceive.LocalId + "'; ";
                        }
                    }
                    sqlMain += @" delete from PB_BomMaterialReceive where Id='" + BomMaterialReceive.Id + "'; ";
                }
            }

            await Db.InsertAsync<IT_LocalDetail>(LocalDetailList);

            await Db.InsertAsync<IT_LocalMaterial>(LocalMaterialList);

            await Db.InsertAsync<PB_StockLog>(StockLogList);

            await ExecuteSqlAsync(sqlMain);
        }

        /// <summary>
        /// 组装件领料提交
        /// </summary>
        /// <param name="DispatchDetailQrCode">派工单明细二维码</param>
        /// <returns></returns>
        [Transactional]
        public async Task SubmitBomMaterialReceiveAsync(string DispatchDetailQrCode)
        {
            string dispatchdetailid = DispatchDetailQrCode.Split(',')?[1].ToString();
            List<PB_BomMaterialReceive> BomMaterialReceive = await Db.GetIQueryable<PB_BomMaterialReceive>().Where(x => x.DispatchDetailId == dispatchdetailid && x.Status == 0).ToListAsync();
            if (BomMaterialReceive.Count > 0)
            {
                List<string> BMids = new List<string>();
                foreach (var item in BomMaterialReceive)
                {
                    BMids.Add(item.Id);
                }

                var BomMaterial = BomMaterialReceive.GroupBy(x => new { x.MaterialId, x.StorId, x.LocalId, x.OldOrNew }).Select(x => new { MaterialId = x.Key.MaterialId, StorId = x.Key.StorId, LocalId = x.Key.LocalId, OldOrNew = x.Key.OldOrNew, Nums = x.Sum(i => i.Nums) }).ToList();
                List<TD_OutStorDetail> OutStorDetail = new List<TD_OutStorDetail>();
                List<TD_OutStorRecord> OutStorRecord = new List<TD_OutStorRecord>();
                string outId = IdHelper.GetId();
                foreach (var item in BomMaterial)
                {
                    PB_Material Material = await Db.GetIQueryable<PB_Material>().Where(x => x.Id == item.MaterialId).FirstOrDefaultAsync();

                    TD_OutStorDetail outdetail = new TD_OutStorDetail();
                    outdetail.Id = IdHelper.GetId();
                    outdetail.StorId = item.StorId;
                    outdetail.OutStorId = outId;
                    outdetail.LocalId = item.LocalId;
                    outdetail.MaterialId = item.MaterialId;
                    outdetail.Price = Convert.ToDouble(Material.Price);
                    outdetail.TotalAmt = Convert.ToDouble(Material.Price * item.Nums);
                    outdetail.OutNum = Convert.ToDouble(item.Nums);
                    outdetail.CreateTime = DateTime.Now;
                    outdetail.CreatorId = _operator.UserId;
                    outdetail.Deleted = false;
                    outdetail.OldOrNew = item.OldOrNew;
                    OutStorDetail.Add(outdetail);
                }

                double? dnum = 0;
                double? dTotalAmt = 0;
                string storid = "";

                var outdetaildata = OutStorDetail.GroupBy(x => new { x.MaterialId, x.StorId, x.Price }).Select(x => new { MaterialId = x.Key.MaterialId, StorId = x.Key.StorId, Price = x.Key.Price, Nums = x.Sum(i => i.OutNum) }).ToList();
                foreach (var item in outdetaildata)
                {
                    storid = item.StorId;
                    TD_OutStorRecord Record = new TD_OutStorRecord();
                    Record.Id = IdHelper.GetId();
                    Record.StorId = item.StorId;
                    Record.OutStorId = outId;
                    Record.MaterialId = item.MaterialId;
                    Record.Price = item.Price;
                    Record.TotalAmt = item.Price * item.Nums;
                    Record.OutNum = item.Nums;
                    Record.CreateTime = DateTime.Now;
                    Record.CreatorId = _operator.UserId;
                    Record.Deleted = false;
                    OutStorRecord.Add(Record);

                    dnum += item.Nums;
                    dTotalAmt += Record.TotalAmt;

                }
                Base_User userdata = await Db.GetIQueryable<Base_User>().Where(x => x.Id == _operator.UserId).FirstOrDefaultAsync();

                PB_DispatchDetail DispatchDetail = await Db.GetIQueryable<PB_DispatchDetail>().Where(x => x.Id == dispatchdetailid).FirstOrDefaultAsync();

                PB_ProductionOrder ProductionOrder = await Db.GetIQueryable<PB_ProductionOrder>().Where(x => x.Id == DispatchDetail.OrderId).FirstOrDefaultAsync();

                TD_OutStorage OutStorage = new TD_OutStorage();
                OutStorage.Id = outId;
                OutStorage.StorageId = storid;
                OutStorage.Code = GetTimeStamp();
                OutStorage.OutTime = DateTime.Now;
                OutStorage.OutType = "AssembleOut";
                OutStorage.RefCode = dispatchdetailid;
                OutStorage.OutNum = dnum;
                OutStorage.TotalAmt = dTotalAmt;
                OutStorage.Status = 0;
                OutStorage.CreateTime = DateTime.Now;
                OutStorage.CreatorId = _operator.UserId;
                OutStorage.Deleted = false;
                OutStorage.QrCode = h_OutStorage + "," + outId;
                OutStorage.DepId = userdata.DepartmentId;
                OutStorage.ProjectId = DispatchDetail == null ? "" : DispatchDetail.ProjectId;
                OutStorage.DesignId = ProductionOrder == null ? "" : ProductionOrder.DesignId;
                OutStorage.FactoryId = userdata.FactoryId;

                string sqlMain = @" update PB_BomMaterialReceive set Status=1 where Id in ('" + string.Join("','", BMids.ToArray()) + "');";

                await Db.InsertAsync<TD_OutStorDetail>(OutStorDetail);

                await Db.InsertAsync<TD_OutStorRecord>(OutStorRecord);

                await Db.InsertAsync<TD_OutStorage>(OutStorage);

                await ExecuteSqlAsync(sqlMain);
            }
            else
            {
                throw new BusException("请领料后提交");
            }
        }

        /// <summary>
        /// 组装件报工获取物料信息
        /// </summary>
        /// <param name="DispatchDetailQrCode">派工单明细二维码</param>
        /// <returns></returns>
        public async Task<BomMaterialWorkDTO> GetBomMaterialWorkDataAsync(string DispatchDetailQrCode)
        {
            //派工单明细Id
            string dispatchdetailid = DispatchDetailQrCode.Split(',')?[1].ToString();

            //获取派工单明细信息
            PB_DispatchDetail DispatchDetail = await Db.GetIQueryable<PB_DispatchDetail>().Where(x => x.Id == dispatchdetailid).FirstOrDefaultAsync();

            //获取派工单信息
            PB_Dispatch Dispatch = await Db.GetIQueryable<PB_Dispatch>().Where(x => x.Id == DispatchDetail.ParentId && x.StaffId.Contains(_operator.UserId)).FirstOrDefaultAsync();
            if (Dispatch == null)
            {
                throw new BusException("请扫描自己的派工单报工");
            }

            //获取组装需要多少物料
            Expression<Func<PB_DispatchDetail, PB_Material, BomMaterialWorkDTO>> select = (a, b) => new BomMaterialWorkDTO
            {
                DispatchDetailId = a.Id,
                MaterialId = a.SubordinateProduct,
                MaterialName = b.Name,
                MaterialCode = b.Code,
                MaterialSpec = b.Spec,
                Nums = a.Nums,
                IsProduct = b.IsProduct,
                ProductCode = b.ProductCode
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<PB_DispatchDetail>().AsExpandable()
                    join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    select @select.Invoke(a, b);
            var res = await q.Where(x => x.DispatchDetailId == dispatchdetailid).FirstOrDefaultAsync();
            List<PB_JobBooking> JobBooking = await Db.GetIQueryable<PB_JobBooking>().Where(x => x.DispatchDetailId == dispatchdetailid).ToListAsync();
            var jobdata = JobBooking.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId, Nums = x.Sum(i => i.Nums) }).FirstOrDefault();
            res.Nums = res.Nums - (jobdata.IsNullOrEmpty() ? 0 : jobdata.Nums);
            return res;
        }

        /// <summary>
        /// 组装件报工
        /// </summary>
        /// <param name="DispatchDetailQrCode">派工单明细二维码</param>
        /// <param name="PrintId">物料码打印机Id</param>
        /// <param name="StorId">仓库Id</param>
        /// <param name="Nums">报工数量</param>
        /// <param name="_mongodbOptions"></param>
        /// <param name="_redisOptions"></param>
        /// <returns></returns>
        public async Task SaveBomMaterialWorkAsync(string DispatchDetailQrCode, string PrintId, string StorId, double? Nums, MongoDBOptions _mongodbOptions, RedisOptions _redisOptions)
        {
            //派工单明细Id
            string dispatchdetailid = DispatchDetailQrCode.Split(',')?[1].ToString();

            if (PrintId.IsNullOrEmpty())
            {
                throw new BusException("请选择打印机");
            }

//            //获取派工单需要领取的物料数量
//            string bomsql = @"select SUM(c.Quantity) as Nums from PB_DispatchDetail as a
//left join Bom_MainNode as b on a.MaterialId = b.MaterailId
//left join Bom_SubNode as c on b.Id = c.ParentId
//where a.Id = '" + dispatchdetailid + "'";
//            var bommaterialnums = await Db.GetListBySqlAsync<BomMaterialReceiveDTO>(bomsql);

//            //获取派工单已领取的物料数量
//            string revesql = @"select SUM(b.Nums) as Nums from PB_DispatchDetail as a
//left join PB_BomMaterialReceive as b on  a.Id=b.DispatchDetailId
//where a.Id='" + dispatchdetailid + "'";
//            var receivenums = await Db.GetListBySqlAsync<BomMaterialReceiveDTO>(revesql);

            //double? dnums = bommaterialnums.FirstOrDefault().Nums == null ? 0 : bommaterialnums.FirstOrDefault().Nums;
            //double? rnums = receivenums.FirstOrDefault().Nums == null ? 0 : receivenums.FirstOrDefault().Nums;
            //if (rnums < dnums)
            //{
            //    throw new BusException("请检查领料是否完成");
            //}

            //TD_Receiving Recedata = await Db.GetIQueryable<TD_Receiving>().Where(x => x.RefCode == dispatchdetailid).FirstOrDefaultAsync();
            //if (Recedata != null)
            //{
            //    throw new BusException("不能重复报工");
            //}

            PB_DispatchDetail DispatchDetail = await Db.GetIQueryable<PB_DispatchDetail>().Where(x => x.Id == dispatchdetailid).FirstOrDefaultAsync();
            if (DispatchDetail.IsRefurbishment != 4)
            {
                throw new BusException("请检查派工单是否是组装件派工");
            }

            //获取派工单信息
            PB_Dispatch Dispatch = await Db.GetIQueryable<PB_Dispatch>().Where(x => x.Id == DispatchDetail.ParentId && x.StaffId.Contains(_operator.UserId)).FirstOrDefaultAsync();
            if (Dispatch == null)
            {
                throw new BusException("请扫描自己的派工单报工");
            }

            List<PB_JobBookingQrCode> JobBookingQrCode = new List<PB_JobBookingQrCode>();
            List<PrintQueue> printq = new List<PrintQueue>();
            //获取组装需要多少物料
            Expression<Func<PB_DispatchDetail, PB_Material, BomMaterialWorkDTO>> select = (a, b) => new BomMaterialWorkDTO
            {
                DispatchDetailId = a.Id,
                MaterialId = a.SubordinateProduct,
                MaterialName = b.Name,
                MaterialCode = b.Code,
                MaterialSpec = b.Spec,
                Nums = a.Nums,
                IsProduct = b.IsProduct,
                ProductCode = b.ProductCode,
                Price = b.Price,
                IsSingle = b.IsSingle
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<PB_DispatchDetail>().AsExpandable()
                    join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    select @select.Invoke(a, b);
            var materiallist = await q.Where(x => x.DispatchDetailId == dispatchdetailid).FirstOrDefaultAsync();

            if (materiallist.IsSingle == false)
            {
                throw new BusException("请确认物料是否为单件扫码");
            }

            Base_User userdata = await Db.GetIQueryable<Base_User>().Where(x => x.Id == _operator.UserId).FirstOrDefaultAsync();

            var deptdata = await _opdept.GetOperatorDept();

            //推送队列物料批次
            string strguid = Guid.NewGuid().ToString("N");

            //收货单Id
            string recId = IdHelper.GetId();

            //实例化Redis操作类
            _redisOptions.DbChannel = (int)Redis_Type.redis6;
            RedisSetHelper redis = new RedisSetHelper(_redisOptions);

            string strnum = string.Empty;

            string sqlMain = string.Empty;

            PB_JobBooking JobBooking = new PB_JobBooking();
            string jobbookid = IdHelper.GetId();
            JobBooking.Id = jobbookid;
            JobBooking.ParentId = DispatchDetail.OrderDetailId;
            JobBooking.Nums = Nums;
            JobBooking.ReturnNums = Nums;
            JobBooking.DeviceId = "";
            JobBooking.DeptId = userdata.FactoryId;
            JobBooking.ReportUserId = _operator.UserId;
            JobBooking.ReportUserName = userdata.RealName;
            JobBooking.ReportTime = DateTime.Now;
            JobBooking.QualityUserId = _operator.UserId;
            JobBooking.QualityUserName = userdata.RealName;
            JobBooking.QualityTime = DateTime.Now;
            JobBooking.StatisticsUserId = _operator.UserId;
            JobBooking.StatisticsUserName = userdata.RealName;
            JobBooking.StatisticsTime = DateTime.Now;
            JobBooking.StatisticsNums = Nums;
            JobBooking.TakeOverUserId = _operator.UserId;
            JobBooking.TakeOverUserName = userdata.RealName;
            JobBooking.TakeOverTime = DateTime.Now;
            JobBooking.CreateTime = DateTime.Now;
            JobBooking.CreatorId = _operator.UserId;
            JobBooking.CreatorRealName = userdata.RealName;
            JobBooking.Status = 2;
            JobBooking.Deleted = false;
            JobBooking.MaterialId = materiallist.MaterialId;
            JobBooking.MaterialName = materiallist.MaterialName;
            JobBooking.WorkshopId = userdata.DepartmentId;
            JobBooking.OldOrNew = 1;
            JobBooking.JobType = 2;  //组装报工
            JobBooking.CirBillId = "";
            JobBooking.DispatchDetailId = materiallist.DispatchDetailId;
            JobBooking.Remarks = "组装件成品报工";


            for (int i = 0; i < Nums; i++)
            {
                //生成成品Id
                PB_JobBookingQrCode jkcode = new PB_JobBookingQrCode();
                string jobcodeId = IdHelper.GetId();   //成品档案表ID
                if (RedisSetHelper.Exists("QualityTestingNumber"))
                {
                    string thedate = DateTime.Now.ToString("yyyy-MM-dd");
                    if (redis.GetString("QualityTestingTime") == thedate)
                    {
                        redis.SetString("QualityTestingNumber", (Convert.ToInt32(redis.GetString("QualityTestingNumber")) + 1).ToString());
                        jkcode.SerialNumber = redis.GetString("QualityTestingNumber").PadLeft(5, '0');
                        strnum = redis.GetString("QualityTestingNumber").PadLeft(5, '0');
                    }
                    else
                    {
                        redis.SetString("QualityTestingTime", thedate);
                        redis.SetString("QualityTestingNumber", "1");
                        jkcode.SerialNumber = redis.GetString("QualityTestingNumber").PadLeft(5, '0');
                        strnum = redis.GetString("QualityTestingNumber").PadLeft(5, '0');
                    }
                }
                else
                {
                    redis.SetString("QualityTestingNumber", "0");
                }
                jkcode.Id = jobcodeId;
                jkcode.ParentId = jobbookid;
                jkcode.OrderId = DispatchDetail.OrderId;
                jkcode.OrderDetailId = DispatchDetail.OrderDetailId;
                jkcode.QrCode = h_Material + "," + DispatchDetail.SubordinateProduct;
                jkcode.MaterialId = DispatchDetail.SubordinateProduct;
                jkcode.MaterialName = materiallist.MaterialName;
                jkcode.OldOrNew = 1;
                jkcode.WorkshopId = userdata.DepartmentId;
                jkcode.RecId = recId;
                jkcode.PackId = "";
                JobBookingQrCode.Add(jkcode);

                //成品码推送
                PrintQueue materprint = new PrintQueue();
                materprint.Id = IdHelper.GetId();
                materprint.No = jobcodeId + "," + strnum;
                materprint.Name = materiallist.MaterialName;
                materprint.TypeName = materiallist.MaterialSpec;
                materprint.Date = DateTime.Now.ToString("yyyy-MM-dd");
                materprint.Dept = deptdata.DeptName;
                materprint.QrCode = h_Material + "," + DispatchDetail.SubordinateProduct;
                materprint.Type = Skill_Type.Statistics.ToString();
                materprint.Status = "0";
                materprint.Printer = PrintId;
                materprint.IsProduct = materiallist.IsProduct;
                materprint.Code = materiallist.IsProduct == true ? materiallist.ProductCode : materiallist.MaterialCode;
                materprint.BatchNumber = strguid;
                materprint.JobCount = Nums;
                materprint.ProjectName = "";
                materprint.BuildingNumberName = "";
                materprint.IsStandard = 1;
                printq.Add(materprint);
            }

            //收货单明细
            TD_RecDetail RecDetail = new TD_RecDetail();
            RecDetail.Id = IdHelper.GetId();
            RecDetail.RecId = recId;
            RecDetail.StorId = StorId;
            RecDetail.MaterialId = materiallist.MaterialId;
            RecDetail.Price = Convert.ToDouble(materiallist.Price);
            RecDetail.PlanNum = Convert.ToDouble(Nums);
            RecDetail.RecNum = Convert.ToDouble(Nums);
            RecDetail.InNum = 0;
            RecDetail.Amount = Convert.ToDouble(Nums * materiallist.Price);
            RecDetail.CreateTime = DateTime.Now;
            RecDetail.CreatorId = _operator.UserId;
            RecDetail.Deleted = false;
            RecDetail.TaskId = DispatchDetail.OrderId;
            RecDetail.CirBillId = "";

            //收货单
            TD_Receiving Receiving = new TD_Receiving();
            Receiving.Id = recId;
            Receiving.StorId = StorId;
            Receiving.Code = GetTimeStamp();
            Receiving.OrderTime = DateTime.Now;
            Receiving.RecTime = DateTime.Now;
            Receiving.Type = "Product";
            Receiving.RefCode = dispatchdetailid;
            Receiving.Status = 3;
            Receiving.TotalNum = Convert.ToDouble(Nums);
            Receiving.InNum = 0;
            Receiving.TotalAmt = Convert.ToDouble(Nums * materiallist.Price);
            Receiving.CreateTime = DateTime.Now;
            Receiving.CreatorId = _operator.UserId;
            Receiving.Deleted = false;
            Receiving.DeptId = userdata.DepartmentId;
            Receiving.QrCode = h_TdRe + "," + recId;
            Receiving.UpdateTime = DateTime.Now;
            Receiving.OrderId = DispatchDetail.OrderId;

            //反写生产任务单已完成数量
            //sqlMain += @" update PB_ProductionOrderDetail set CompleteNums=IFNULL(CompleteNums,0)+" + materiallist.Nums + " where Id='" + DispatchDetail.OrderDetailId + "'; ";

            sqlMain += @" update PB_DispatchDetail set WorkReportQuantity=IFNULL(WorkReportQuantity,0)+" + Nums + " where Id='" + DispatchDetail.Id + "'; ";

            MongoDBHelper mongo = new MongoDBHelper(_mongodbOptions);
            //插入mongo队列
            await mongo.InsertManyAsync<PrintQueue>(PrintQueueCollection, printq);

            await Db.InsertAsync<PB_JobBookingQrCode>(JobBookingQrCode);

            await Db.InsertAsync<PB_JobBooking>(JobBooking);

            await Db.InsertAsync<TD_RecDetail>(RecDetail);

            await Db.InsertAsync<TD_Receiving>(Receiving);

            await ExecuteSqlAsync(sqlMain);

        }

        /// <summary>
        /// 组装报工选择物料打印机
        /// </summary>
        /// <returns></returns>
        public async Task<List<PrintBomMaterialDTO>> GetPrintBomMaterialAsync()
        {
            //获取登录人工厂下的所有部门Id
            List<string> deptids = await _opdept.GetFactoryIds();

            string sqlMain = @"select a.Id as PrintId,CONCAT(a.`Name`,'(',b.`Name`,')') as PrintName from PB_Printer as a left join Base_Department as b on a.WorkshopId=b.Id  where a.Type='PrintMaterial' and a.WorkShopId in ('" + string.Join("','", deptids.ToArray()) + "')";
            return await Db.GetListBySqlAsync<PrintBomMaterialDTO>(sqlMain);
        }

        /// <summary>
        /// 组装报工选择包码打印机
        /// </summary>
        /// <returns></returns>
        public async Task<List<PrintBomMaterialDTO>> GetPrintBomPackAsync()
        {
            //获取登录人工厂下的所有部门Id
            List<string> deptids = await _opdept.GetFactoryIds();

            string sqlMain = @"select a.Id as PrintId,CONCAT(a.`Name`,'(',b.`Name`,')') as PrintName from PB_Printer as a left join Base_Department as b on a.WorkshopId=b.Id  where a.Type='PrintPackage' and a.WorkShopId in ('" + string.Join("','", deptids.ToArray()) + "')";
            return await Db.GetListBySqlAsync<PrintBomMaterialDTO>(sqlMain);
        }

        #endregion

        #region 报工报废生成新派工单

        /// <summary>
        /// 质检补制
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public async Task AddScrappingProducts(ScrappingProducts data) {

            String sqlmain = String.Empty;
            //获取用户数据
            var user = await Db.GetIQueryable<Base_User>().Where(m => m.Id == _operator.UserId).FirstOrDefaultAsync();
            List<PB_Dispatch> Dispatchlist = new List<PB_Dispatch>();
            List<PB_DispatchDetail> DispatchDetaillist = new List<PB_DispatchDetail>();
             var userlist = data.SubordinateProductList.GroupBy(x => new { x.StaffId, x.StaffName, x.TeamType, x.DispatchDate }).Select(x => new { StaffId = x.Key.StaffId, StaffName = x.Key.StaffName, TeamType = x.Key.TeamType, DispatchDate = x.Key.DispatchDate }).ToList();
                    var pb_circulationBill = await Db.GetIQueryable<PB_CirculationBill>().Where(m => m.Id == data.Id).FirstOrDefaultAsync();
                    if (pb_circulationBill != null)
                    {
                var disdetail =await Db.GetIQueryable<PB_DispatchDetail>().Where(m=>m.Id== pb_circulationBill.DispatchDetailId).FirstOrDefaultAsync();
                foreach (var item in userlist)
                {
                    PB_Dispatch Dispatch = new PB_Dispatch();
                    string DispatchId = IdHelper.GetId();
                    Dispatch.Id = DispatchId;
                    Dispatch.StaffId = item.StaffId;
                    Dispatch.StaffName = item.StaffName;
                    #endregion
                    Dispatch.WorkOrderNo = IdHelper.GetId();
                    Dispatch.DeptId = user.DepartmentId;
                    Dispatch.TeamType = item.TeamType;
                    Dispatch.DispatchDate = item.DispatchDate;
                    Dispatch.CreatorId = _operator.UserId;
                    Dispatch.CreateTime = DateTime.Now;
                    Dispatch.CreatorRealName = user.RealName;
                    Dispatch.Deleted = false;
                    Dispatch.QrCode = h_Dispatch + "," + DispatchId;
                    Dispatch.State = 2;
                   
                    Dispatch.CirBillId = pb_circulationBill.Id;
                    Dispatchlist.Add(Dispatch);
                    var itemdata = data.SubordinateProductList.Where(x => x.StaffId == item.StaffId && x.DispatchDate == item.DispatchDate).ToList();
                    foreach (var items in itemdata)
                    {
                        PB_DispatchDetail DispatchDetail = new PB_DispatchDetail();
                        string DetailId = IdHelper.GetId();
                        DispatchDetail.Id = DetailId;
                        DispatchDetail.ParentId = DispatchId;
                        DispatchDetail.IsRefurbishment = disdetail.IsRefurbishment;
                        DispatchDetail.SubId = items.SubId;
                        DispatchDetail.ProjectId = disdetail.ProjectId;
                        DispatchDetail.MaterialId = items.MaterialId;
                        DispatchDetail.OrderId = disdetail.OrderId;
                        DispatchDetail.OrderDetailId = disdetail.OrderDetailId;
                        DispatchDetail.Aperture = items.Aperture;
                        DispatchDetail.Texture = items.Texture;
                        DispatchDetail.Nums = items.Nums;
                        DispatchDetail.QrCode = h_DispatchDetail + "," + DetailId;
                        DispatchDetail.CreatorId = _operator.UserId;
                        DispatchDetail.CreateTime = DateTime.Now;
                        DispatchDetail.Deleted = false;
                        DispatchDetail.CreatorRealName = user.RealName;
                        DispatchDetail.WorkReportQuantity = 0;
                        DispatchDetail.ReadyNums = 0;
                        DispatchDetail.SubordinateProduct = disdetail.SubordinateProduct;
                        DispatchDetail.MaterialLength = items.MaterialLength;
                        DispatchDetail.PunchingNums = items.PunchingNums;
                        DispatchDetail.PunchingPosition = items.PunchingPosition;
                        DispatchDetaillist.Add(DispatchDetail);
                    }
                }

                if (pb_circulationBill.DispatchRemark.IsNullOrEmpty()) {
                    sqlmain = "update PB_CirculationBill SET DispatchRemark='已补制,'  WHERE Id='" + pb_circulationBill.Id + "' ";
                    await Db.ExecuteSqlAsync(sqlmain);
                }
                else {
                    if (pb_circulationBill.DispatchRemark.Contains("已补制"))
                    {
                    }
                    else
                    {

                        if (!pb_circulationBill.DispatchRemark.IsNullOrEmpty())
                        {
                            sqlmain = "update PB_CirculationBill SET DispatchRemark='已补制,'  WHERE Id='" + pb_circulationBill.Id + "' ";
                            await Db.ExecuteSqlAsync(sqlmain);
                        }
                        else
                        {
                            sqlmain = "update PB_CirculationBill SET DispatchRemark='已补制,'  WHERE Id='" + pb_circulationBill.Id + "' ";
                            await Db.ExecuteSqlAsync(sqlmain);
                        }
                    }
                }
                await Db.InsertAsync<PB_DispatchDetail>(DispatchDetaillist);
                await Db.InsertAsync<PB_Dispatch>(Dispatchlist);
                
            }           
        }




        /// <summary>
        /// 质检改制
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public async Task AddProductsDetailReform(ProductsDetailReform data)
        {

            String sqlmain = String.Empty;
            string sql = string.Empty;
            //获取用户数据
            var user = await Db.GetIQueryable<Base_User>().Where(m => m.Id == _operator.UserId).FirstOrDefaultAsync();
            var pb_circulationBill = await Db.GetIQueryable<PB_CirculationBill>().Where(m => m.Id == data.Id).FirstOrDefaultAsync();
            if (pb_circulationBill != null)
            {
                var pB_ProductionOrderdata =await Db.GetIQueryable<PB_ProductionOrder>().Where(m=>m.Id== pb_circulationBill.TaskId).FirstOrDefaultAsync();
                //创建生产任务单
                PB_ProductionOrder pB_ProductionOrder = new PB_ProductionOrder();
                string taskId = IdHelper.GetId();
                pB_ProductionOrder.Id = taskId;
                pB_ProductionOrder.Code = IdHelper.GetId();
                pB_ProductionOrder.DesignId = pB_ProductionOrderdata.DesignId;
                pB_ProductionOrder.POName= pB_ProductionOrderdata.POName+"自生产("+DateTime.Now+")";
                pB_ProductionOrder.Type = pB_ProductionOrderdata.Type;
                pB_ProductionOrder.BeginDate = pB_ProductionOrderdata.BeginDate;
                pB_ProductionOrder.EndDate = pB_ProductionOrderdata.EndDate;
                pB_ProductionOrder.Remarks = pB_ProductionOrderdata.Remarks;
                pB_ProductionOrder.DeptId = pB_ProductionOrderdata.DeptId;
                pB_ProductionOrder.BatchNumber = null;
                pB_ProductionOrder.State = pB_ProductionOrderdata.State;
                pB_ProductionOrder.CreateTime = DateTime.Now;
                pB_ProductionOrder.CreatorId = _operator.UserId;
                pB_ProductionOrder.Deleted = false;
                pB_ProductionOrder.Status = 1;
                pB_ProductionOrder.IsNew = false;
                pB_ProductionOrder.QrCode = h_ProductionOrder + "," + taskId;//二维码;
                pB_ProductionOrder.IsRefurbishment = 5;
                pB_ProductionOrder.Taskstatus = 0;

                PB_ProductionOrderDetail pB_ProductionOrderDetail = new PB_ProductionOrderDetail();
                string taskdetailId = IdHelper.GetId();
                pB_ProductionOrderDetail.Id = taskdetailId;
                pB_ProductionOrderDetail.MaterialId = data.SubordinateProduct;
                pB_ProductionOrderDetail.ParentId = taskId;
                pB_ProductionOrderDetail.ProcecureId = null;
                pB_ProductionOrderDetail.Nums = data.ProductNums;
                pB_ProductionOrderDetail.Remarks = pB_ProductionOrderdata.Remarks;
                pB_ProductionOrderDetail.StartDate = pB_ProductionOrderdata.BeginDate;
                pB_ProductionOrderDetail.EndDate = Convert.ToDateTime(pB_ProductionOrderdata.EndDate);
                pB_ProductionOrderDetail.CreateTime = DateTime.Now;
                pB_ProductionOrderDetail.CreatorId = _operator.UserId;
                pB_ProductionOrderDetail.Deleted = false;
                pB_ProductionOrderDetail.HasCheck =false;
                pB_ProductionOrderDetail.QrCode = h_ProductionOrderDetail + "," + taskId + "," + taskdetailId;
                pB_ProductionOrderDetail.WorkshopId = user.DepartmentId;
                pB_ProductionOrderDetail.StationId = null;
                pB_ProductionOrderDetail.Finishedproduct = data.SubordinateProduct;
                pB_ProductionOrderDetail.Ofmaterial = null;
                pB_ProductionOrderDetail.Holespacing = null;
                pB_ProductionOrderDetail.Productionquantity = data.ProductNums;
                pB_ProductionOrderDetail.CompleteNums = 0;
                pB_ProductionOrderDetail.State = 0;
                    PB_Dispatch Dispatch = new PB_Dispatch();
                    string DispatchId = IdHelper.GetId();
                    Dispatch.Id = DispatchId;
                    Dispatch.StaffId = data.StaffId;
                    Dispatch.StaffName = data.StaffName;
                    Dispatch.WorkOrderNo = IdHelper.GetId();
                    Dispatch.DeptId = user.DepartmentId;
                    Dispatch.TeamType = data.TeamType;
                    Dispatch.DispatchDate = data.DispatchDate;
                    Dispatch.CreatorId = _operator.UserId;
                    Dispatch.CreateTime = DateTime.Now;
                    Dispatch.CreatorRealName = user.RealName;
                    Dispatch.Deleted = false;
                    Dispatch.QrCode = h_Dispatch + "," + DispatchId;
                    Dispatch.State = 3;
      
                    Dispatch.CirBillId = pb_circulationBill.Id;
                PB_DispatchDetail DispatchDetail = new PB_DispatchDetail();
                    string DetailId = IdHelper.GetId();
                     DispatchDetail.Id = DetailId;
                     DispatchDetail.ParentId = DispatchId;
                        DispatchDetail.IsRefurbishment = 5;
                        DispatchDetail.SubId = data.SubId;
                if (!pB_ProductionOrderdata.DesignId.IsNullOrEmpty()) {
                    var pb_designlist =await Db.GetIQueryable<PB_DesignList>().Where(m=>m.Id== pB_ProductionOrderdata.DesignId).FirstOrDefaultAsync();
                    DispatchDetail.ProjectId = pb_designlist.ProjectId;
                }
                else{
                    DispatchDetail.ProjectId = null;

                } 
                        DispatchDetail.MaterialId = data.MaterialId;
                        DispatchDetail.OrderId = taskId;
                        DispatchDetail.OrderDetailId =taskdetailId;
                        DispatchDetail.Aperture =null;
                        DispatchDetail.Texture = null;
                        DispatchDetail.Nums = data.DispatchNums;
                        DispatchDetail.QrCode = h_DispatchDetail + "," + DetailId;
                        DispatchDetail.CreatorId = _operator.UserId;
                        DispatchDetail.CreateTime = DateTime.Now;
                        DispatchDetail.Deleted = false;
                        DispatchDetail.CreatorRealName = user.RealName;
                        DispatchDetail.WorkReportQuantity = 0;
                        DispatchDetail.ReadyNums = 0;
                        DispatchDetail.SubordinateProduct = data.SubordinateProduct;
                        DispatchDetail.MaterialLength = null;
                        DispatchDetail.PunchingNums = null;
                        DispatchDetail.PunchingPosition = null;
                if (pb_circulationBill.DispatchRemark.IsNullOrEmpty()) {
                    sqlmain = "update PB_CirculationBill SET DispatchRemark='已改制,'  WHERE Id='" + pb_circulationBill.Id + "' ";
                    await Db.ExecuteSqlAsync(sqlmain);
                }
                else {
                    if (pb_circulationBill.DispatchRemark.Contains("已改制"))
                    {
                    }
                    else
                    {

                        if (!pb_circulationBill.DispatchRemark.IsNullOrEmpty())
                        {
                            sqlmain = "update PB_CirculationBill SET DispatchRemark=concat(DispatchRemark,'已改制,')  WHERE Id='" + pb_circulationBill.Id + "' ";
                            await Db.ExecuteSqlAsync(sqlmain);
                        }
                        else
                        {
                            sqlmain = "update PB_CirculationBill SET DispatchRemark='已改制,'  WHERE Id='" + pb_circulationBill.Id + "' ";
                            await Db.ExecuteSqlAsync(sqlmain);
                        }
                    }
                }
                
                await Db.InsertAsync<PB_ProductionOrder>(pB_ProductionOrder);

                await Db.InsertAsync<PB_ProductionOrderDetail>(pB_ProductionOrderDetail);

                await Db.InsertAsync<PB_Dispatch>(Dispatch);

                await Db.InsertAsync<PB_DispatchDetail>(DispatchDetail);
             
            }
            
            }
        }
    }
